Multiple Joins with a twist

Question

Multiple Joins with a twist

Andy Dillbeck

SSC Journeyman

Points: 79
More actions
January 29, 2010 at 7:19 am

#218611

I have two main tables and a connecting table that I want to join together.
The first table is Customers:
CustomerID, CustomerName
The second table is Product Types
ProductID, ProductName
And the connecting table holds the info to join them together:
CatID, CustomerID, ProductID
It's pretty simple to join them together and see what customers have received which products, but They want to see what products a customer hasn't received.
I know I can do it in the programming code (for each Customer{ for each product{ not exists in table(customerid and productid)}}) but it would be nice to know how to do this in SQL.
does anyone have any ideas?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Without table defs (CREATE TABLE statements), sample data (as a series of INSERT INTO statements), all I could come up with is this:

select

c.CustomerID,

c.CustomerName,

p.ProductID,

p.ProductName

from

dbo.Customer c

cross join dbo.Product p

where

not exists(select 1 from dbo.CustomerProduct cp where cp.CustomerID = c.CustomerID and cp.ProductID = p.ProductID);

Lowell SSC Guru Points: 323518 More actions · Answer 2

there's two concepts you'll want to use here to get the desired results:

using a CROSS JOIN can give you every possible combination.

using a LEFT OUTER JOIN can show you results that do not exist in your connecting table.

--all possible combinations

SELECT * FROM

(

SELECT

Customers.CustomerID,

Customers.CustomerName,

[Product Types].ProductID

[Product Types].ProductName

FROM Customers

CROSS JOIN [Product Types] --cross join gets every possible value

) MyCombinedAlias

LEFT OUTER JOIN [connecting table]

ON MyCombinedAlias.CustomerID = [connecting table].CustomerID

AND MyCombinedAlias.ProductID = [connecting table].ProductID

WHERE [connecting table].CatID IS NULL --if it doesn't exist in the connecting table so far

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Andy Dillbeck SSC Journeyman Points: 79 More actions · Answer 3

Thats awesome. Somehow I had forgotten about cross joins. If you don't use it you lose it I guess.

So yeah, that'll work perfectly. Thanks a ton

Arthur Kirchner Ten Centuries Points: 1239 More actions · Answer 4

Hi Lynn,

not exists(select 1 from dbo.CustomerProduct cp where cp.CustomerID = c.CustomerID and cp.ProductID = p.ProductID);

can you explain, why you use "1" in the exists-statement instead of "*"?

I also know some code examples with: "count(1)", but never understood, if this led to any performance enhancement in the old days

or still does, or if there is another reason...

Thanks in advance!

Arthur

Andy Dillbeck SSC Journeyman Points: 79 More actions · Answer 5

In this case (and correct me if I'm wrong), because you are checking if something doesn't exists, 1 is like true. You don't need to return *, you just want to know if something is present at all, and do something if it isn't.

NOT EXISTS works the opposite as EXISTS. The WHERE clause in NOT EXISTS is satisfied if no rows are returned by the subquery.

So you don't have to have it return a bunch of stuff. a canned 1 will work just fine, because it will only show up if something is going to be returned.

Arthur Kirchner Ten Centuries Points: 1239 More actions · Answer 6

Thank you for your fast reply!

So you don't have to have it return a bunch of stuff. a canned 1 will work just fine, because it will only show up if something is going to be returned.

NOT EXISTS Returns TRUE if a subquery contains 0 rows. So using '1' is more a matter of style?

So I could use

select * from table1.column1 t1 where exists (select 'Yay!' from table2 where column2=t1.column1)

to have a little laugh while I'm scripting?

Andy Dillbeck SSC Journeyman Points: 79 More actions · Answer 7

Tested 'Yay!' with not exists and it works just as good as 1.

I'll be the first to admit that I'm not a SQL master, so I don't know if there is any kind of performance gains to one versus the other, but yes, it does work.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

To make things a little clearer, I hope, the following are equivalent:

select * from dbo.Customer cus where exists(select * from dbo.Order ord where ord.CustomerID = cus.CustomerID);

select * from dbo.Customer cus where exists(select 1 from dbo.Order ord where ord.CustomerID = cus.CustomerID);

In the first, SQL Server determines that it really doesn't need to return any data from the table, so it will generate a plan like the one the second will; or so I have been told.