Try instead: select distinct etc.
will only work if your select only involves the patient table. RBS > Hi, > > Thanks for your quick replies. I have tried this method but however I am > getting a row returned for each entry in ForcedSpiroTable or > RelaxedSpiroTable that matches the search criteria. > > i.e. If a single patient say "Joe Bloggs" has 5 tests, all with EVC and > FVC greater than 2.0 then I get > > Joe Bloggs > Joe Bloggs > Joe Bloggs > Joe Bloggs > Joe Bloggs > > What I want is Joe Blogs just the once. > > Does this make sense? What I need to do is find all patients that have an > EVC and FVC greater than 2.0. > > Is there a way to do this? Am I missing something? > > Thanks again > > Mark > > >> -----Original Message----- >> From: Dennis Cote [mailto:[EMAIL PROTECTED] >> Sent: 01 May 2007 15:31 >> To: sqlite-users@sqlite.org >> Subject: Re: [sqlite] SQL query help (mutiple joins) >> >> >> 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] >> -------------------------------------------------------------- >> --------------- >> >> >> >> >> >> > > > DISCLAIMER: > This information and any attachments contained in this email message is > intended only for the use of the individual or entity to which it is > addressed and may contain information that is privileged, confidential, > and exempt from disclosure under applicable law. If the reader of this > message is not the intended recipient, or the employee or agent > responsible for delivering the message to the intended recipient, you are > hereby notified that any dissemination, distribution, forwarding, or > copying of this communication is strictly prohibited. If you have > received this communication in error, please notify the sender immediately > by return email, and delete the original message immediately. > > ----------------------------------------------------------------------------- > To unsubscribe, send email to [EMAIL PROTECTED] > ----------------------------------------------------------------------------- > > > ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
