sg4 sg4 - 4 months ago 9
SQL Question

SQL: Only show values depending on contents

This is a complicated question so please bear with me. So I have the table below:

╔══════════╦═══════╦════════════╗
║ MasterID ║ SubID ║ Location ║
╠══════════╬═══════╬════════════╣
║ 100 ║ 50 ║ California ║
║ 100 ║ 45 ║ Texas ║
║ 100 ║ 40 ║ California ║
║ 99 ║ 29 ║ California ║
║ 99 ║ 28 ║ Texas ║
║ 99 ║ 28 ║ California ║
║ 98 ║ 15 ║ Texas ║
║ 98 ║ 15 ║ Texas ║
║ 97 ║ 5 ║ California ║
╚══════════╩═══════╩════════════╝


.

And I want to display every MasterID, SubID, and Location where the Location is California. However, here's the catch. I don't want those things displayed unless, within each MasterID, every SubID has a California Location.

Where MasterID = 100, I would not want anything displayed, because there is a Texas, with its own SubID, within that MasterID.

Where MasterID = 99, I would want all 3 columns displayed, because, although there is a Texas under that MasterID, there is also a California within the same SubID.

98 is not good because it has SubIDs that contain only Texas.

97 is ok because it's just California.

The desired table outcome is this:

╔══════════╦═══════╦════════════╗
║ MasterID ║ SubID ║ Location ║
╠══════════╬═══════╬════════════╣
║ 99 ║ 29 ║ California ║
║ 99 ║ 28 ║ Texas ║
║ 99 ║ 28 ║ California ║
║ 97 ║ 5 ║ California ║
╚══════════╩═══════╩════════════╝


(Removing 99......28.....Texas is fine too)

Answer

This should do:

SELECT A.*
FROM dbo.YourTable A
INNER JOIN (SELECT  MasterID,
                    COUNT(DISTINCT SubID) SubIDs,
                    COUNT(DISTINCT CASE WHEN Location = 'California' THEN SubID END) CaliforniaSubIDs
            FROM dbo.YourTable
            GROUP BY MasterID) B
    ON A.MasterID = B.MasterID
WHERE B.SubIDs = B.CaliforniaSubIDs;

Here you can see the query in action. And the results are:

╔══════════╦═══════╦════════════╗
║ MasterID ║ SubID ║  Location  ║
╠══════════╬═══════╬════════════╣
║       97 ║     5 ║ California ║
║       99 ║    29 ║ California ║
║       99 ║    28 ║ Texas      ║
║       99 ║    28 ║ California ║
╚══════════╩═══════╩════════════╝