|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Simple "AND" query - how to do it?I have a table with medication info for patients. Fields include record #,
medication name, dosage, start and stop dates, etc. If a patient is on 6 meds, there are 6 records, 2 meds - 2 records, etc., etc. I want to run a query that lists patients that are on both Medication X AND Medication Y. It seems like it should be easy enough, but I’m stumped. select mrno, drugname, startyear from table where drugname= "Vioxx" OR drugname = "Mobic" order by mrno of course lists patients who are on one or the other drug. But how can I whittle that down to those who are on BOTH? Thanks. select mrno, drugname, startyear from table where
mrno in (select mrno from table where drugname= "Vioxx" AND drugname = "Mobic") order by mrno I am assuming that mrno is the patient name. Archer Show quote "BKE" wrote: > I have a table with medication info for patients. Fields include record #, > medication name, dosage, start and stop dates, etc. If a patient is on 6 > meds, there are 6 records, 2 meds - 2 records, etc., etc. > > I want to run a query that lists patients that are on both Medication X AND > Medication Y. It seems like it should be easy enough, but I’m stumped. > > select mrno, drugname, startyear from table where drugname= "Vioxx" OR > drugname = "Mobic" order by mrno > of course lists patients who are on one or the other drug. But how can I > whittle that down to those who are on BOTH? > > Thanks. > bagman3rd wrote:
> select mrno, drugname, startyear from table where It's medical record number.> mrno in > (select mrno from table where drugname= "Vioxx" AND drugname = "Mobic") > order by mrno > > I am assuming that mrno is the patient name. I think your inner select statement is not going to return anything. To me the solution look something like this: SELECT T1.mrno, T1.drugname FROM DataTable T1 INNER JOIN DataTable T2 ON T1.mrno = T2.mrno WHERE T1.drugname = 'Vioxx' AND T2.drugname = 'Mobic' Show quote > Archer > > "BKE" wrote: > > >>I have a table with medication info for patients. Fields include record #, >>medication name, dosage, start and stop dates, etc. If a patient is on 6 >>meds, there are 6 records, 2 meds - 2 records, etc., etc. >> >>I want to run a query that lists patients that are on both Medication X AND >>Medication Y. It seems like it should be easy enough, but I’m stumped. >> >>select mrno, drugname, startyear from table where drugname= "Vioxx" OR >>drugname = "Mobic" order by mrno >>of course lists patients who are on one or the other drug. But how can I >>whittle that down to those who are on BOTH? >> >>Thanks. >> Thanks very much for your help. It looks like this is going to work for me.
Billie --------------------------- Show quote "Sericinus hunter" wrote: > bagman3rd wrote: > > select mrno, drugname, startyear from table where > > mrno in > > (select mrno from table where drugname= "Vioxx" AND drugname = "Mobic") > > order by mrno > > > > I am assuming that mrno is the patient name. > > It's medical record number. > I think your inner select statement is not going to return anything. > To me the solution look something like this: > > SELECT T1.mrno, T1.drugname FROM DataTable T1 INNER JOIN DataTable T2 > ON T1.mrno = T2.mrno > WHERE T1.drugname = 'Vioxx' AND T2.drugname = 'Mobic' > > > Archer > > > > "BKE" wrote: > > > > > >>I have a table with medication info for patients. Fields include record #, > >>medication name, dosage, start and stop dates, etc. If a patient is on 6 > >>meds, there are 6 records, 2 meds - 2 records, etc., etc. > >> > >>I want to run a query that lists patients that are on both Medication X AND > >>Medication Y. It seems like it should be easy enough, but I’m stumped. > >> > >>select mrno, drugname, startyear from table where drugname= "Vioxx" OR > >>drugname = "Mobic" order by mrno > >>of course lists patients who are on one or the other drug. But how can I > >>whittle that down to those who are on BOTH? > >> > >>Thanks. > >> > Thanks, Archer. I appreciate your response. However, this code didn't work
for me. It returned no records. Billie PS. MRNO is the patient's ID number, same idea as last name. ---------------------------------------- Show quote "bagman3rd" wrote: > select mrno, drugname, startyear from table where > mrno in > (select mrno from table where drugname= "Vioxx" AND drugname = "Mobic") > order by mrno > > I am assuming that mrno is the patient name. > > Archer > > "BKE" wrote: > > > I have a table with medication info for patients. Fields include record #, > > medication name, dosage, start and stop dates, etc. If a patient is on 6 > > meds, there are 6 records, 2 meds - 2 records, etc., etc. > > > > I want to run a query that lists patients that are on both Medication X AND > > Medication Y. It seems like it should be easy enough, but I’m stumped. > > > > select mrno, drugname, startyear from table where drugname= "Vioxx" OR > > drugname = "Mobic" order by mrno > > of course lists patients who are on one or the other drug. But how can I > > whittle that down to those who are on BOTH? > > > > Thanks. > > > Thanks, Archer. I appreciate your response. However, this code didn't work ONCE AGAIN.> for me. It returned no records. Please post DDL, sample data and desired results. See http://www.aspfaq.com/5006 for info. Hi. I got your DDL, etc. message the first time. It takes time to get that
all together, especially the INSERT statements. I'll post it as soon as I get it together.... unless someone else posts a solution in the meantime. Thanks for your help. -------------------------------------------------------------------- Show quote "Aaron Bertrand [SQL Server MVP]" wrote: > > Thanks, Archer. I appreciate your response. However, this code didn't work > > for me. It returned no records. > > ONCE AGAIN. > Please post DDL, sample data and desired results. > See http://www.aspfaq.com/5006 for info. > > > I'm making a lot of assumptions about the data model and I'm not sure
how good this code is. I could be way off base here. Posting DDL could help here. select m1.mrno, m1.drugname, m1.startyear from mytable m1 join (select mrno from mytable where drugname = 'Vioxx') m2 on m1.mrno = m2.mrno join (select mrno from mytable where drugname = 'Mobic') m3 on m2.mrno = m3.mrno order by m1.mrno Show quote "BKE" <B**@discussions.microsoft.com> wrote in message news:26933188-1A9E-490C-AAA5-B92C68400D28@microsoft.com... > Thanks, Archer. I appreciate your response. However, this code didn't work > for me. It returned no records. > Billie > PS. MRNO is the patient's ID number, same idea as last name. > > ---------------------------------------- > > "bagman3rd" wrote: > > > select mrno, drugname, startyear from table where > > mrno in > > (select mrno from table where drugname= "Vioxx" AND drugname = "Mobic") > > order by mrno > > > > I am assuming that mrno is the patient name. > > > > Archer > > > > "BKE" wrote: > > > > > I have a table with medication info for patients. Fields include record #, > > > medication name, dosage, start and stop dates, etc. If a patient is on 6 > > > meds, there are 6 records, 2 meds - 2 records, etc., etc. > > > > > > I want to run a query that lists patients that are on both Medication X AND > > > Medication Y. It seems like it should be easy enough, but I'm stumped. > > > > > > select mrno, drugname, startyear from table where drugname= "Vioxx" OR > > > drugname = "Mobic" order by mrno > > > of course lists patients who are on one or the other drug. But how can I > > > whittle that down to those who are on BOTH? > > > > > > Thanks. > > > Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info. Show quote "BKE" <B**@discussions.microsoft.com> wrote in message news:75E4F6E7-5B56-48B4-865D-DF1EE00D04BF@microsoft.com... >I have a table with medication info for patients. Fields include record #, > medication name, dosage, start and stop dates, etc. If a patient is on 6 > meds, there are 6 records, 2 meds - 2 records, etc., etc. > > I want to run a query that lists patients that are on both Medication X > AND > Medication Y. It seems like it should be easy enough, but I’m stumped. > > select mrno, drugname, startyear from table where drugname= "Vioxx" OR > drugname = "Mobic" order by mrno > of course lists patients who are on one or the other drug. But how can I > whittle that down to those who are on BOTH? > > Thanks. > select mrno from T
where drugname in ('Vioxx', 'Mobic') group by mrno having min(drugname) < max(drugname) The following will generalize better, but may be less efficient for this particular query: .... having count(distinct drugname) = 2 Steve Kass Drew University BKE wrote: Show quote >I have a table with medication info for patients. Fields include record #, >medication name, dosage, start and stop dates, etc. If a patient is on 6 >meds, there are 6 records, 2 meds - 2 records, etc., etc. > >I want to run a query that lists patients that are on both Medication X AND >Medication Y. It seems like it should be easy enough, but I’m stumped. > >select mrno, drugname, startyear from table where drugname= "Vioxx" OR >drugname = "Mobic" order by mrno >of course lists patients who are on one or the other drug. But how can I >whittle that down to those who are on BOTH? > >Thanks. > > > Look up the term Relational Division. You can then use an list of
drugs as the divisor.  |
|||||||||||||||||||||||
Other interesting topics