Join of 4 tables

[schockp]

Hi,

After a few years of not using SQL, I have some problems to find the right query. There are 4 tables:
Substance: ID, Name
SubstanceProperty: ID, SubstanceID, Value, PropertyPropertyGroupID
PropertyPropertyGroup: ID, PropertyID
Property: ID, Code

In each table 'ID' is the primary key. SubstanceID links to Substance.ID, PropertyPropertyGroupID to PropertyPropertyGroup.ID and PropertyPropertyGroupID.PropertyID to Property.ID. Now I want to select the Code of the Property, the Value of the PropertyPropertyGroup and the Name of a Substance for a specific Substance.ID.

How can I handle this?

thx,
Pieter

[PinkPanther2003]

SELECT P.Code, PPG.ID, S.Name
FROM Substance as S
INNER JOIN SubstanceProperty as SP
ON S.ID = SP.SubstanceID
INNER JOIN PropertyPropertyGroup PPG
ON PPG.ID = SP.PropertyPropertyGroupID
INNER JOIN Property P
ON P.ID = PPG.ID

Should do the trick if the RDBMS (which you didn't mention which one it was) supports joins (I would be very surprise if it didn't but some don't).

HTH,

Peter