Query Advice - Probably simple

Hi,
Quite new to SQL. I have been asked to query two tables to find which combinations of CustomerID and ProductID exist in one but not the other. I am concatenating the fields in the query to produce a combination I can query ( see below)

This seems to work but takes along time - I'm sure there must be a better way of doing this? Please bear in mind I am a novice in your responses!

select distinct(ProductID),CustomerID from customerpackproduct where

str(ProductID) + str(CustomerID) not in

(select str(ProductID) + str(CustomerID) from customerproduct)

order by customerid

You can use LEFT OUTER JOIN customerpackproduct with customerproduct on ProductID and CustomerID. Any rows with NULLs in these two columns are the rows you are looking for.

Thanks,

Could you demonstrate the syntax for me?

Look in books online for syntax.

select distinct cpp.ProductID, cpp.CustomerID from customerpackproduct as cpp
left outer join customerproduct as cp
on cpp.ProductID = cp.ProductID
and cpp.CustomerID = cp.CustomerID
where cp.CustomerID is NULL
and cp.ProductID is NULL
order by cpp.customerid

Many thanks for your help

I think full outer join would be better in this instance, as both tables can be checked in a single query...

select distinct cpp.ProductID AS cpp_ProductId, cpp.CustomerID AS cpp_CustomerID, cp.ProductId AS cp_ProductID, cp.CustomerID AS cp_CustomerID from customerpackproduct as cpp
full outer join customerproduct as cp
on cpp.ProductID = cp.ProductID
and cpp.CustomerID = cp.CustomerID
where (cp.CustomerID is NULL and cp.ProductID is NULL)
OR (cpp.customerId IS NULL AND cpp.productId IS NULL)

select str(ProductID), str(CustomerID)
from customerproduct a
where EXISTS
(Select 1
from customerpackproduct b
where a.CustomerID=b.CustomerID
AND a.ProductID=b.ProductID)
order by str(CustomerID)

One more thing ... This will work the fastest if u have indexing On CustomerId and ProductId in customerpackproduct table.