How to create Correlated Subquery? 2

[blackbirdMIT]

I would like to display each oil (the oilID and oilName) and the property (propertyID and propertyName) with the highest propertyID associated with it. I want the results by the oilID. The highest propertyID or number means it's more associated with an oil compared to a property with a lower propertyID.

Tables:
OILS table
OilID - PK, int(4)
OilName - nvarchar(50)

PROPERTIES table
PropertyID - PK, int(4)
PropertyName - nvarchar(50)

OILPROPERTIES table
OilID - int(4)
PropertyID - FK, int(4)


How do I do the subquery?

 

[r937]

[tt]select O.OilID, OilName, P.propertyID, PropertyName
from OILS O
inner
join OILPROPERTIES OP
on O.OilID = OP.OilID
inner
join PROPERTIES P
on OP.PropertyID = P.PropertyID
where OP.PropertyID =
( select max(PropertyID)
from OILPROPERTIES
where OilID = OP.OilID ) -- correlated
order
by O.OilID[/tt]

rudy

http://r937.com/

 

[Transcend]

This worked for me:

select oils.oilid, oilname, properties.propertyid, propertyname
from oils
inner join oilproperties on oils.oilid = oilproperties.oilid
inner join properties on oilproperties.propertyid = properties.propertyid
where properties.propertyid in
(select max(properties.propertyid) from oils
inner join oilproperties on oils.oilid = oilproperties.oilid
inner join properties on oilproperties.propertyid = properties.propertyid
group by oilname)

Transcend

 

[blackbirdMIT]

Do you know of another to accomplish the same results using a different method (not correlated subquery)?

 

[ClaireHCM]

Correlated query is very neat.You should try to avoid using to much join and alias tables

 

[blackbirdMIT]

Can we do a Join for this requirement? Any advantage on using the correlated subquery instead of using Join as far as performance issues?