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 |
|
kneel
Starting Member
36 Posts |
 Posted - 2008-05-22 : 13:03:55
|
| Hello All,I hv one doubt while writing query. There is one table as Consumers. In this table there are columns as Consumer_id,Interest_Code_id.One consumer can have multiple interest codes like 100,101,102...110.I am writing query to retrive consumers which do not have interest code 102,103,104 as follows. SELECT * FROM CONSUMERS WHERE INTEREST_CODE_ID NOT IN (102,103,104)But one of the code(102,103,104) is associated to one of the consumer. So it is giving me wrong results.Do anyone have any idea how to resolve this query ?Thanks in advance--kneel |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-05-22 : 13:08:09
|
| Are you looking for customers who dont have any of these interest ids?If yes, use thisSELECT Consumer_id FROM CONSUMERS GROUP BY Consumer_id HAVING SUM(CASE WHEN Interest_Code_id IN (102,103,104) THEN 1 ELSE 0 END)=0 |
 |
|
|
|
|
|
query