NOT exists query with conditons

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

NOT exists query with conditons

Author

Topic

andrewcw
Posting Yak Master

133 Posts

Posted - 2011-09-02 : 23:47:58

Consider this example

Column J Column K Column Date
fred beans 1/10/2011
fred eggs 1/20/2010
sam beans
sam eggs
sam bread
sam chocolate

Is there a way to return fred because all the names that dont contain bread & chocolate and other conditions based on other columns

andrewcw

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-03 : 05:29:06

[code]
select ColumnJ
FROM Table
GROUP BY ColumnJ
HAVING COUNT(DISTINCT CASE WHEN ColumnK in ('bread','chocolate') THEN ColumnK ELSE NULL END) =2
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

andrewcw
Posting Yak Master

133 Posts

Posted - 2011-09-03 : 10:53:52

Thank you. I failed to model my question correctly. I think its clearest if I can downselect from the 2 tables and then query for the missing items like this
[Table Orders]->Select Customer from [Orders] as FC where ODate > '1/01/2011'
[Table Status]->Select Distinct Customer from as TS [Status] where ODate -> '1/01/2011' and Product like ‘C%’

Finally to feed outputs of both queries above into this:

SELECT FC.customer FROM FC AS a WHERE NOT EXISTS (SELECT * FROM TS AS b WHERE b.customer = a.customer)

This flow can be drawn easily, but I did not understand how to load an image. Can you show me how to properly nest these queries or do I need make temp tables ?

Many Thanks...

andrewcw

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-03 : 12:36:04

[code]SELECT f.customer
FROM FC f
LEFT JOIN
(
Select Customer from [Orders] as FC where ODate > '1/01/2011'
UNION
Select Customer from as TS [Status] where ODate -> '1/01/2011' and Product like ‘C%’
)t
ON t.Customer = f.Customer
WHERE t.Customer IS NULL
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

andrewcw
Posting Yak Master

133 Posts

Posted - 2011-09-03 : 13:29:45

Thanks - but still issues:
The section within the Join looks ok. But with SQL server I get some syntax errors:
SELECT f.customer -> the multipart identifier f.customer could not be found
FROM FC -> invalid Object Name FC
LEFT JOIN
Select Customer from [Orders] as FC where ODate > '1/01/2011'
UNION
Select Customer from [Status] as TS where ODate -> '1/01/2011' and Product like 'C%'
)t
ON t.Customer = f.Customer -> the multipart identifier f.customer could not be found

[ note the tables/column names slightly different : but the errors seem to be from not being able ot identify FC... ]

And Thank you very much for looking at this !!!

andrewcw

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources