SQL: How to relate duplicate rows to unique rows and remove them from another table?

Question

Currently I have two tables Table_A and Table_B.

Table_A

ID

1
2
2
3
3
4

Table_B

ID   Alphabet
1       X
1       Y
2       X
2       Y
2       Z
3       X
3       Z
4       X

I want to group the column ID and Alphabet from Table_B and find out which group in the combination has the alphabet Z in it.

Then whichever group has Z in it, its ID would not be displayed when I look up IDs for Table_A.

Expected Result

ID 
1
4

I'm currently using this SELECT statement:

SELECT A.ID FROM Table_A A LEFT JOIN Table_B B 
ON A.ID = B.ID WHERE A.ID NOT IN (SELECT B.ID FROM Table_B 
WHERE Alphabet = 'Z' GROUP BY B.ID, Alphabet)

Actual Result

ID 
1
1
2
2
3
4

It removes the IDs that has the Alphabet Z in it but it does not relate to other duplicate IDs.

Answer 1

what about this:

select id 
from table_a
where not exists (
  select 1 from table_b 
  where table_b.id = table_a.id 
  and table_b.alphabet = 'Z')

Answer 2

Use this concatenation to create group alphabet

How can I combine multiple rows into a comma-delimited list in Oracle?

then you will have

   id    alphabet
   1     x,y
   2     x,y,z
   3     x,z
   4     x

then

SELECT id
FROM newQuery
WHERE INSTR(LOWER(alphabet), 'z') = 0;

Answer 3

If you are using oracle, you can use LISTAGG function for concatenating values using commas. You can refer to the code below :

SELECT 
                    id ,
                    LISTAGG((alphabet)|| ',' ORDER BY id)  (VARCHAR(1000))  alphabet_list
            from 
            table
            group by 
                    id   
            ;

Then remove the records having Z using INSTR.

Answer 4

You want one record per ID, so you group by ID. Then you want information on the IDs data and want to limit your results accordingly, so you use a HAVING clause. Count Zs per ID and only keep IDs where the number of found Zs is 0.

select id
from table2
group by id
having count(case when alphabet = 'Z' then 1 end) = 0;

This is only an alternative by the way. The straight-forward way is NOT EXISTS (or NOT IN) as shown by Frank Ockenfuss. My solution has the advantage that you only have to read one table, but the disadvantage of only showing IDs present in table2. So you see there are alternatives, but you should better go with Frank's answer here :-) I only wrote this because I saw you were trying to do something with GROUP BY yourself.