How do I pass an inner query value to the outer query to be used in its query in sql?
I need to get a record out of the ASSET table but I only have the parent assetnum to be able to find the entry i need.There are 2 entries with the same assetnum but one has its parent filled with the parent assetnum value i have and one doesn't. I need to get the record of the one that doesn't have the parent field populated. I was thinking i could get the one entry that has the parent field populated, get its assetnum then use that assetnum to query the table again but this time adding "parent is null" and retrieve the record i want who's parent field is empty. So far i have:
select assetnum from asset where assetnum in(select assetnum from asset where parent = (parentassetnum) and siteid=(siteid))and parent is null
So in this query i was hoping to snatch that assetnum from the inner query and use it in my outer query. That inner query only returns one record and i think what is happening is that my outer query is queried against the results on the inner query and not using the assetnum for its own outer query. Is what i want to do possible in SQL?
Thanks for you help in advance. Wann
It looks to me like if you change the "in" to an = sign, it should work.
SELECT assetnum FROM asset WHERE assetnum = (SELECT assetnum FROM asset WHERE parent = (parentassetnum) AND siteid=(siteid)) AND parent IS NULL