Re: [sqlite] SQL query help

[Paul Sanderson]

Hmm thanks Roger

Table could have a few million rows, i'll have a play and see what the
run time is. The relevant column is indexed

On 20 August 2011 17:14, Roger Andersson  wrote:
>  On 08/20/11 05:42 PM, Paul Sanderson wrote:
>> Hi all
>>
>> I am trying to create a query that works to craete a subset of a table
>> based on duplicate items
>>
>> Examples work best so consider the contrived table with the following rows
>> 10 socata
>> 7 socata
>> 13 cessna
>> 2 piper
>> 7 piper
>> 55 piper
>> 1 diamond
>>
>> I want to see the subset that is
>> 10 socata
>> 7 socata
>> 2 piper
>> 7 piper
>> 55 piper
>>
>> i.e. all rows that have a matching value in any other row in the second 
>> column
>>
>> any ideas?
>> ___
> Might be more efficient queries if there is a LOT of records but this
> seems to do the trick.
>  create table tbl (id,text);
>  insert into tbl values (10, 'socata');
>  insert into tbl values (7, 'socata');
>  insert into tbl values (13, 'cessna');
>  insert into tbl values (2, 'piper');
>  insert into tbl values (7, 'piper');
>  insert into tbl values (55,'piper');
>  insert into tbl values (1, 'diamond');
>  select * from tbl where text in (select text from tbl group by text
> having count(*) > 1);
> 10|socata
> 7|socata
> 2|piper
> 7|piper
> 55|piper
>
> Cheers Roger
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Paul Sanderson
Sanderson Forensics
+44 (0)1326 572786
www.sandersonforensics.com
http://www.twitter.com/sandersonforens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL query help

[Roger Andersson]

  On 08/20/11 05:42 PM, Paul Sanderson wrote:
> Hi all
>
> I am trying to create a query that works to craete a subset of a table
> based on duplicate items
>
> Examples work best so consider the contrived table with the following rows
> 10 socata
> 7 socata
> 13 cessna
> 2 piper
> 7 piper
> 55 piper
> 1 diamond
>
> I want to see the subset that is
> 10 socata
> 7 socata
> 2 piper
> 7 piper
> 55 piper
>
> i.e. all rows that have a matching value in any other row in the second column
>
> any ideas?
> ___
Might be more efficient queries if there is a LOT of records but this 
seems to do the trick.
  create table tbl (id,text);
  insert into tbl values (10, 'socata');
  insert into tbl values (7, 'socata');
  insert into tbl values (13, 'cessna');
  insert into tbl values (2, 'piper');
  insert into tbl values (7, 'piper');
  insert into tbl values (55,'piper');
  insert into tbl values (1, 'diamond');
  select * from tbl where text in (select text from tbl group by text 
having count(*) > 1);
10|socata
7|socata
2|piper
7|piper
55|piper

Cheers Roger
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL query help

[Jeff Godfrey]

- Original Message - 
From: "P Kishor" <[EMAIL PROTECTED]>

To: <sqlite-users@sqlite.org>
Sent: Monday, June 18, 2007 2:55 PM
Subject: Re: [sqlite] SQL query help



On 6/18/07, Jeff Godfrey <[EMAIL PROTECTED]> wrote:



I have a table which contains (among other things), a "name" column
and a "version" column (a software asset table).  I need a query 
that

will group all like "names" together in a single record, and return
the latest "version" (the largest value) for each group.  What I 
have



Jeff, how about something like

SELECT name, MAX(version) AS latest
FROM asset
GROUP BY name


Thanks Puneet - that's just what I needed.

Side note - the timing on the list seems to be whacky right now (at 
least for me), so some of my responses seem to be coming out of order. 
Sorry if that causes some confusion...


Thanks again.

Jeff 



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] SQL query help

[P Kishor]

On 6/18/07, Jeff Godfrey <[EMAIL PROTECTED]> wrote:

Not seeing this on the list 1.5 hrs after posting, I thought I'd try
again.  Sorry if this is a duplicate...

Jeff

=

Hi All,

I have a table which contains (among other things), a "name" column
and a "version" column (a software asset table).  I need a query that
will group all like "names" together in a single record, and return
the latest "version" (the largest value) for each group.  What I have
so far is this:

SELECT name, version
FROM asset
GROUP BY name
ORDER BY name ASC, version DESC

While the above seems to return the expected results, I'm not
convinced that I'm actually controlling the sort order, as changing
"version DESC" to "version ASC" does not return the *earliest* version
as I'd expect.  I assume the record that will be returned has already
been selected at the "GROUP BY" stage and therefore I have no control
over it at the "ORDER BY" stage?  I know, I need to do some more
reading... ;^)

Thanks for any input.


Jeff, how about something like

SELECT name, MAX(version) AS latest
FROM asset
GROUP BY name

--
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/
S Policy Fellow, National Academy of Sciences http://www.nas.edu/
-
collaborate, communicate, compete
=

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] SQL query help...

[Jeff Godfrey]

- Original Message - 
From: "Trey Mack" <[EMAIL PROTECTED]>

To: <sqlite-users@sqlite.org>
Sent: Friday, June 08, 2007 1:08 PM
Subject: Re: [sqlite] SQL query help...



Here's what I tried, which didn't work...

select
name,
substr(name,1,length(name)-3) as zone,
substr(name,length(name)-2,2) as location,
max(thick) - min(thick) as diff from plypoint
where diff > 0.0005
group by zone,location

That causes a "misuse of aggregate" error.


select
name,
substr(name,1,length(name)-3) as zone,
substr(name,length(name)-2,2) as location,
max(thick) - min(thick) as diff from plypoint
group by zone,location
having diff > 0.0005;



Trey,

That seems to work great.  I appreciate the assistance - thank you.

Jeff



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] SQL query help...

[Trey Mack]

Here's what I tried, which didn't work...

select
name,
substr(name,1,length(name)-3) as zone,
substr(name,length(name)-2,2) as location,
max(thick) - min(thick) as diff from plypoint
where diff > 0.0005
group by zone,location

That causes a "misuse of aggregate" error.


select
name,
substr(name,1,length(name)-3) as zone,
substr(name,length(name)-2,2) as location,
max(thick) - min(thick) as diff from plypoint
group by zone,location
having diff > 0.0005;

- tmack

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] SQL query help (mutiple joins)

[bartsmissaert]

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]
-

Re: [sqlite] SQL query help (mutiple joins)

[P Kishor]

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)

[Allan, Mark]

> 
> 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)

[bartsmissaert]

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)

[Allan, Mark]

> 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)

[Dennis Cote]

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)

[Dennis Cote]

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]

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, 

RE: [sqlite] SQL query help (mutiple joins)

[bartsmissaert]

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)

[P Kishor]

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)

[Allan, Mark]

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)

[Dennis Cote]

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)

[bartsmissaert]

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]
-