-
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)
-
Reverting the Query
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|