On 5/1/07, Allan, Mark <[EMAIL PROTECTED]> wrote:
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?
Yes, it makes sense.
Yes, there is a way to do this.
Yes, you are missing something.
You have to realize there is a difference between what the SQL returns
and what you expect/want to see. The SQL is being very truthful --
since your condition for Joe Bloggs is satisfied 5 times, Joe Bloggs
is returned 5 times. That is precisely what you want. Imagine it this
way --
If you had to create a table in a spreadsheet that would depict the
returned result, how many rows would you have for Joe Bloggs? Of
course, five.
However, you _want_ to display Joe Bloggs only once. Well, for that
you have to do something else.
If you want to stick to SQL, well, you can query the returned results
and SELECT DISTINCT on the patient's name. Or, if you have an
application, you can grab the entire result set, and reformat it using
your favorite programming language.
See, there is a difference between SQL's result and your expectation.
A SQL always returns a "table" -- a rectangular, rows x cols selection
where every cell is filled with something even if that "something"
might be "null"
Hope this helps.
> -----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]
-----------------------------------------------------------------------------
--
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Geospatial Foundation http://www.osgeo.org/education/
---------------------------------------------------------------------
collaborate, communicate, compete
=====================================================================
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
