I have a table with top 3 products per industry
I also have a customer table with products they have purchased including what industry they belong to
I need a query which will join the tables and only return products not associated with the customer based on industry ie. CID 001 belongs to the Retail industry, it currently has product copper. Based on the industry table it is missing Silver and Gold.
Really stuck with this one and any help would be appreciated.
SQL 2016
Thanks in Advance
FYI.. having some issues with the formatting, 1st table below is the Industry, 2nd table is the Customer, 3rd is the Result(just for CID 001) of the query needed
| Industry | Product |
| ------------ | ------------ |
| Retail | Copper |
| Retail | Silver |
| Retail | Gold |
| Oil | Blue |
| Oil | Red |
| Oil | Gold |
CID | Industry | Product |
---|---|---|
001 | Retail | Copper |
002 | Retail | Silver |
002 | Retail | Gold |
003 | Oil | Blue |
004 | Oil | Red |
005 | Oil | Gold |
CID | Industry | Product |
------------ | ------------ | ------------ |
001 | Retail | Silver |
001 | Retail | Gold |
2条答案
按热度按时间41ik7eoe1#
The simple way is to use
except
.In the first select you add all products using
cross apply
and then remove all you have inCustomer
usingexcept
eqqqjvef2#
Not sure if this helps, but if rename your second table to Orders, and add a Customers table that has the CID and Industry of the Customer, you could do the following