Select items missing a specific identifier

jdr0606
jdr0606 used Ask the Experts™
on
SQL query to return items missing specific locations.

I have a database with thousands of parts that are broken down by part number and location.  A single item can be in the table multiple times and with a different location.

I'm trying to identify which parts are missing a specific location
i.e.
If I look at all the Parts where there is not a location=North

in the below example Part3 and Part4 have no location=North

PartNumber      Location
Part1      East
Part1      West
Part1      South
Part1      North
Part2      East
Part2      West
Part2      South
Part2      North
Part3      East
Part3      West
Part3      South
Part4      West
Part4      South
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
SELECT PartNumber,
    MAX(CASE WHEN Location = 'East' THEN 1 ELSE 0 END) AS [East],
    MAX(CASE WHEN Location = 'North' THEN 1 ELSE 0 END) AS [North],
    MAX(CASE WHEN Location = 'South' THEN 1 ELSE 0 END) AS [South],
    MAX(CASE WHEN Location = 'West' THEN 1 ELSE 0 END) AS [West]
FROM dbo.tablename
GROUP BY PartNumber
HAVING MAX(CASE WHEN Location = 'East' THEN 1 ELSE 0 END) = 0 OR
    MAX(CASE WHEN Location = 'North' THEN 1 ELSE 0 END) = 0 OR
    MAX(CASE WHEN Location = 'South' THEN 1 ELSE 0 END) = 0 OR
    MAX(CASE WHEN Location = 'West' THEN 1 ELSE 0 END) = 0
ORDER BY PartNumber

Author

Commented:
Easily adapted to my exact need

Thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial