SQL Server Get Values where records not found

3duebb1j 于 2023-06-21 发布在其他

关注(0)|答案(2)|浏览(145)

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

sql-server

来源：https://stackoverflow.com/questions/76441935/get-values-where-records-not-found

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 in Customer using except

select c.CID, c.Industry, i.Product from Customer c
cross apply Industry i
where c.Industry = i.Industry
except
select CID, Industry, Product from Customer

赞(0）回复(0）举报 2023-06-21

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

SELECT C.CID,
    I.Industry,
    I.product
FROM Industry I
INNER JOIN Customers C ON C.Industry = I.Industry
WHERE NOT EXISTS (
        SELECT O.CID
        FROM Orders O
        WHERE O.cid = C.cid
            AND O.Product = I.Product
        )
ORDER BY C.cid,
    I.Industry,
    I.Product

赞(0）回复(0）举报 2023-06-21

我来回答

SQL Server Get Values where records not found

2条答案

相关问题

热门标签

最新问答