[sqlite] SQL query help (mutiple joins)
Hi,
Can anyone offer any help with the following SQL query?
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.
Your help would be gratefully received
Thanks in advance
Mark
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.
Re: [sqlite] SQL query help (mutiple joins)
select *
from PatientsTable P
inner join ForcedSpiroTable F on
(P.ID = F.ID)
inner join RelaxedSpiroTable R on
(P.ID = R.ID)
where
F.EVC 2.0 and
R.FVC 2.0
RBS
Hi,
Can anyone offer any help with the following SQL query?
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.
Your help would be gratefully received
Thanks in advance
Mark
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]
-
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]
-
RE: [sqlite] SQL query help (mutiple joins)
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]
-
Re: [sqlite] SQL query help (mutiple joins)
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]
-
RE: [sqlite] SQL query help (mutiple joins)
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]
-
RE: [sqlite] SQL query help (mutiple joins)
Excellent, thanks for your help.
Indeed I was missing the DISTINCT keyword. The query does exactly what I need
it to now.
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of P
Kishor
Sent: 01 May 2007 15:50
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] SQL query help (mutiple joins)
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
Re: [sqlite] SQL query help (mutiple joins)
Allan, Mark wrote: What I want is Joe Blogs just the once. Mark, Then try adding distinct like this: select distinct 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] -
Re: [sqlite] SQL query help (mutiple joins)
Allan, Mark wrote: Excellent, thanks for your help. Mark, For future reference, your posts could use a little more trimming. There is no need to quote the entire string of messages from your original post on each reply. :-) Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] SQL query help (mutiple joins)
Allan, Mark wrote: What I want is Joe Blogs just the once. Mark, Then try adding distinct like this: select distinct 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; Ok, so here's another question, how would I get the count of patients where the EVC and FVC 2.0? 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] -
RE: [sqlite] SQL query help (mutiple joins)
It will be as the below query, but replace: distinct p.* with: count(p.ID) RBS Allan, Mark wrote: What I want is Joe Blogs just the once. Mark, Then try adding distinct like this: select distinct 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; Ok, so here's another question, how would I get the count of patients where the EVC and FVC 2.0? 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] -
RE: [sqlite] SQL query help (mutiple joins)
Ok, so here's another question, how would I get the count of patients where the EVC and FVC 2.0? Dont worry I have figured this out. I am doing:- select count (distinct p.PatientID) 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; 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] -
Re: [sqlite] SQL query help (mutiple joins)
actually SELECT COUNT(DISTINCT ... On 5/1/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: It will be as the below query, but replace: distinct p.* with: count(p.ID) RBS Allan, Mark wrote: What I want is Joe Blogs just the once. Mark, Then try adding distinct like this: select distinct 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; Ok, so here's another question, how would I get the count of patients where the EVC and FVC 2.0? -- 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] -
Re: [sqlite] SQL query help (mutiple joins)
Yes, you are right. Good thing the OP found it himself. RBS actually SELECT COUNT(DISTINCT ... On 5/1/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: It will be as the below query, but replace: distinct p.* with: count(p.ID) RBS Allan, Mark wrote: What I want is Joe Blogs just the once. Mark, Then try adding distinct like this: select distinct 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; Ok, so here's another question, how would I get the count of patients where the EVC and FVC 2.0? -- 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] - - To unsubscribe, send email to [EMAIL PROTECTED] -
