Find the discrepancies in sql (tricky situation)-CodePudding

Im stuck on this situation and i do not really know what to do in Oracle

I am using Oracle database

Suppose that i have a table called "MYTABLE" and it contains the tuples of every dog in the world and the owers:

|NAME     |OWNER_ID|
|Aaron    |81     |
|Aaron    |281    |
|Aaron    |404    |
|Michael  |81     |
|Michael  |281    |
|Michael  |404    |
|Brendan  |281    |
|Brendan  |81     |
|Micon    |404    |
|Micon    |81     |
|Tyson    |404    |

For DEFAULT every DOG must be associated with 3 different owners, in this case the owners are identified by an id: 81, 281 and 404.

How can i know what are the dogs that are not associated with 3 rows in the table?

I mean, i would like to obtain this output:

Brendan
Micon
Tyson

This is because these 3 dogs do not have 3 rows in the given table. They are not associated exactly with owners 81, 281 and 404.

CodePudding user response：

To find out which name is not associated with all three of the 81, 281 and 404 owner_id, you can use conditional aggregation in a HAVING clause:

SELECT name
FROM   table_name
GROUP BY name
HAVING COUNT(DISTINCT CASE WHEN OWNER_ID IN (81, 281, 404) THEN OWNER_ID END) < 3

Which, for the sample data:

CREATE TABLE table_name (NAME, OWNER_ID) AS
SELECT 'Aaron',    81 FROM DUAL UNION ALL
SELECT 'Aaron',   281 FROM DUAL UNION ALL
SELECT 'Aaron',   404 FROM DUAL UNION ALL
SELECT 'Michael',  81 FROM DUAL UNION ALL
SELECT 'Michael', 281 FROM DUAL UNION ALL
SELECT 'Michael', 404 FROM DUAL UNION ALL
SELECT 'Brendan', 281 FROM DUAL UNION ALL
SELECT 'Brendan',  81 FROM DUAL UNION ALL
SELECT 'Brendan', 123 FROM DUAL UNION ALL -- Different owner_id
SELECT 'Micon',   404 FROM DUAL UNION ALL
SELECT 'Micon',    81 FROM DUAL UNION ALL
SELECT 'Micon',    81 FROM DUAL UNION ALL -- Duplicate
SELECT 'Tyson',   404 FROM DUAL

Outputs:

NAME

Brendan

Micon

Tyson

NAME
Brendan
Micon
Tyson

If you just want the name where they do not have three different owner_id (any owner_id) then:

SELECT name
FROM   table_name
GROUP BY name
HAVING COUNT(DISTINCT OWNER_ID) < 3

Which, for the same sample data, would output:

NAME

Micon

Tyson

NAME
Micon
Tyson

If you just want the name where there are not three owner_id (either unique or not) then:

SELECT name
FROM   table_name
GROUP BY name
HAVING COUNT(OWNER_ID) < 3

Which, for the same sample data, outputs:

NAME

Tyson

NAME
Tyson

db<>fiddle here

CodePudding user response：

You can GROUP BY the NAME and the use COUNT(*) to exclude al that have less than 3 owners

SELECT "NAME" FROM tab1 GROUP BY "NAME" HAVING COUNT(*) < 3

| NAME    |
| :------ |
| Brendan |
| Micon   |
| Tyson   |

db<>fiddle here