Allan, Mark wrote:
I have a database that looks something like the following:-PatientsTable { ID, Name, Sex, ....} ExaminationsTable { ID, PatientID, ....} TestTable { ID, ExamID, .....} ForcedSpiroTable { ID, TestID, EVC, IVC, IC ... } RelaxedSpiroTable { ID, TestID, FVC, FEV1, PEF, ...} Can someone help me out with the syntax for applying a search for all rows in the PatientsTable that have a ForcedSpiroTable.EVC > 2.0 and a RelaxedSpiroTable.FVC > 2.0? Basically ExaminationsTable has a foreign key to PatientsTable, TestsTable has a foreign key to ExaminationsTable and both ForcedSpiroTable and RelaxedSpiroTable have a foreign key to the TestTable.
Mark, This should do the trick: select p.* from PatientsTable as p join ExaminationsTable as e on e.PatientID=p.ID join TestTable as t on t.ExamID=e.ID join ForcedSpiroTable as f on f.TestID=t.ID join RelaxedSpiroTable as r on r.TestID=t.ID where f.EVC > 2.0 and r.FVC > 2.0; HTH Dennis Cote ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
