SQL Statement: Seems Easy but isn't: Use IN?

[RichieRich99]

Hi all,

I was asked for this query and thought it would be easy but I've struggled with it for more that two hours now.

For simplicity purposes, I have the following table:

Table: CD
Member ID, Code, Date
100, MINF03, 03/30/2005
101, MINF04, 03/30/2005
102, MINF04, 03/30/2005
102, MINF05, 03/30/2005
102, MINF04, 03/31/2005
103, MINF04, 03/31/2005
104, MINF03, 03/31/2005

The primary key is all 3 columns.

What I need is all records where Code equals MINF04 except those where there is also a code of MINF05 on the same day.

So taking the above records, my resultset would be:

Member ID, Code, Date
101, MINF04, 03/30/2005
102, MINF04, 03/31/2005
103, MINF04, 03/31/2005


I've tried a mess of options including using IN, GROUP BY, COUNT but just can't get it. Thanks in advance all.

Rick

 

[fredericofonseca]

please show us the code you've tried so far. If possible a few variants.

Then we will be able to point out where you are in error.


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[RichieRich99]

No worries. I actually got it to work a couple of minutes after I posted. Just doing extensive testing now. Here it is for those interested:


select * from cfprddta.cd1
where cdcode = 'MINF04'
AND memberid IN (
select memberid
from cfprddta.cd1
where (cdcode = 'MINF04' or cdcode = 'MINF05')
group by memberid
having count(distinct cdcode) = 1
)

 

[PHV]

Another way:
SELECT A.*
FROM cfprddta.cd1 A LEFT JOIN cfprddta.cd1 B
ON A.memberid = B.memberid AND A.Date = B.Date AND B.cdcode = 'MINF05'
WHERE A.cdcode = 'MINF04' AND B.memberid IS NULL

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[karluk]

RitchieRich99,
I don't think your query gives you the results you want. You never refer to the "date" column in your query, so you will get no rows returned from the following data.

102, MINF04, 03/31/2005
102, MINF05, 03/30/2005

According to your selection criteria, however, you should get the "102, MINF04, 03/31/2005" row because there is no MINF05 row on the same date.

Your code would work if the primary key were on only the two columns MemberID and Code, but unfortunately the key includes Date as well.

 

[dnoeth]

A translation of your narrative to SQL

select *
from cfprddta.cd1 t1
where cdcode = 'MINF04'
AND NOT EXISTS
(select *
from cfprddta.cd1 t2
where t1.memberid = t2.memberid
and t1.date = t2.date
and t2.cdcode = 'MINF05'
)

Dieter