Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Spionred
Starting Member
8 Posts |
 Posted - 2009-05-21 : 21:14:04
|
| Hi,I have a table where a customer may exist in a numeber of rows, each one with a differnt code.I have a query that can list each cutomer that has a record with a given code, that's easy. What I am trying to achive is a list of cutomer with a given code but if that ALSO have a second code they are excluded.For example customers with a code EUR to be listed unless that also have a record with a code US. so I want csutomer who have been un Europe but not in the US.Hope this makes sense :-)So any ideas?cheers in advance...Kevin |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2009-05-21 : 21:15:23
|
can you provide your table DDL and sample data with the expected result ?
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
Spionred
Starting Member
8 Posts |
Posted - 2009-05-21 : 21:34:56
|
| not really, the tabel is quite complex and contains medical information. I am using an example to give you the gist of what I am trying to do.Cust | Code------------- A | EUR A | US B | EUR C | EUR C | US D | US E | EURThe result should only show B dn E as they are the only ones who have EUR and DONT have US |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2009-05-21 : 21:49:05
|
[code]select *from yourtable twhere t.Code = 'EUR'and not exists ( select * from yourtable x where x.Cust = t.Cust and x.Code = 'US' )[/code]
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
Spionred
Starting Member
8 Posts |
Posted - 2009-05-21 : 21:52:39
|
| Fantastic!Thak you very much.Kevin. |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2009-05-21 : 21:54:49
|
you are welcome
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-05-22 : 03:33:00
|
| [code]select Custfrom yourtable GROUP BY CustHAVING SUM(CASE WHEN Code='EUR' THEN 1 ELSE 0 END) >0AND SUM(CASE WHEN Code='US' THEN 1 ELSE 0 END)=0[/code] |
|
|
|
|
|
|
testing for existance of another record