SQL - How to get Distinct Values from a Column that are Unique to an Entry in another Column?

Question

I work in a factory that makes Microchips. We make these Microchips in Batches of 10,000. One of the Machines that the Microchip Batches are processed on is called 'Dispense'. There are 30 of these 'Dispense' machines - named Dispense 1, Dispense 2, Dispense 28 etc.

These Machines often 'Alarm' if there is an issue when processing the Microchips. The different Alarms are assigned different 'Codes'.

So Alarm Code 1 may be 'Kill Switch left on'. Alarm Code 2 may be 'Guard Door still open' etc.

In an ideal World, All Dispense Machines would have the same suite of Alarm Codes. But they don't.

They are certain Alarm Codes that are Unique to Dispense 1 for example, that do not occur on the other Dispense machines.

The Alarms data is fed into a table called 'dispense_status' like the below:

machine	alarm_code	run_timestamp	batch_number
Dispense_1	1	23-07-2021 14:32	1000585855
Dispense_23	4	12-09-2021 12:34	1000585856
Dispense_9	7	11-08-2021 13:33	1000456789
Dispense_1	2	09-09-2021 15:55	1000223774
Dispense_9	2	18-05-2021 12:34	1000374590

In the above Table, Alarm Code 1 is unique to Dispense 1. Alarm Code 2 occurs on Dispense 1 also, but it is not unique to Dispense 1, as this alarm_code also occurs on Dispense 9.

If I write:

SELECT DISTINCT ALARM_CODE
FROM DISPENSE_STATUS
WHERE MACHINE = DISPENSE_1
ORDER BY ALARM_CODE

This will give me:

alarm_code
1
2

But I only want Alarm Code 1, as this is the only Alarm Code unique to Dispense 1.

(Alarm Code 2 also occurs on Dispense 9).

What is a SQL query to give me only Alarm Codes unique to Dispense 1?

Answer 1

You can use a HAVING clause to check if it occurs on more than one machine, and check if that occurence is unique to that specific machine:

SELECT alarm_code
FROM dispense_status
GROUP BY alarm_code
HAVING COUNT(DISTINCT machine) = 1 AND MAX(machine) = 'Dispense_1'
ORDER BY alarm_code;

Answer 2

Use a SQL query with a subquery to compare Alarm Codes for Dispense_1 with those of other machines:

SELECT DISTINCT ds1.ALARM_CODE
FROM DISPENSE_STATUS ds1
WHERE MACHINE = 'Dispense_1'
AND NOT EXISTS (
    SELECT 1
    FROM DISPENSE_STATUS ds2
    WHERE ds2.ALARM_CODE = ds1.ALARM_CODE
    AND ds2.MACHINE != 'Dispense_1'
);

We start with a SELECT DISTINCT statement to retrieve the unique Alarm Codes for Dispense_1. We use NOT EXISTS in a subquery to check for any other records (ds2) with the same Alarm Code but on a different machine. If there are no such records, it means the Alarm Code is unique to Dispense_1.

Answer 3

A somewhat forgotten feature, EXCEPT:

SELECT ALARM_CODE FROM DISPENSE_STATUS WHERE MACHINE = 'DISPENSE_1'
EXCEPT
SELECT ALARM_CODE FROM DISPENSE_STATUS WHERE MACHINE <> 'DISPENSE_1'