Joining Correlating SubQuerys - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Joining Correlating SubQuerys

Author

Topic

tjwent69
Starting Member

30 Posts

Posted - 2007-07-11 : 16:25:42

I am currently trying to produce a list of new product placements for June. This means that customer has purchased a product for the first time in June if they purchased it before June it doesn't count as a placement.

I have two SubQuery's.

Query 1:
Select customer
From CustomerOrders
Where CustomerOrders are in June and Item is 123456.

I get customers 1234 and 5678.

Query 2:
Select customer
From CustomerOrders
Where CustomerOrders are in May and Item is 123456.

I get customer 1234

So... 1234 purchased the item before June so they don't count as new placements.

For my report I want to have only customer 5678 display.

Joining Query1 to Query2
Inner Joins get me 1234.
Left outer joins get me 1234 and 5678
Right outer joins get me 1234 and a bunch of NULLS.

Joining Query2 to Query1 gets me the same thing.

Its like I need a reverse join or an excluded something.
I am extremely frustrated that I cannot figure this out.

Any suggestions or ideas would really really be appreciated.

mattyblah
Starting Member

49 Posts

Posted - 2007-07-11 : 16:58:01

Could you do something like:


Select customer 
From CustomerOrders c1
Where CustomerOrders are in June and Item is 123456
and not exists
(Select *
From CustomerOrders c2
Where c1.customer = c2.customer and CustomerOrders are in May and Item is 123456)

tjwent69
Starting Member

30 Posts

Posted - 2007-07-11 : 17:26:43

That did it!

Exists was the magic word, thanks.

I didn't even know it existed. So I have never used it before.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources