Caadi0 Caadi0 - 6 months ago 8
SQL Question

Return results only if it doesn't contain a particular value?

I have 2 tables :-

Table

T


ID | val
1 | abcd
2 | 1234
3 | asd
4 | lkj


And another table
M


ID | T_ID | Type
1 | 1 | I
2 | 1 | S
3 | 2 | I
4 | 2 | I
5 | 3 | I
6 | 4 | S


I want to write a query that joins table
T
and
M
on
m.T_ID = T.ID
but it should not return
T.ID
if any
M
mapped to it has Type
S
i.e. the above set of data should return values
T.ID = 2,3
and not
1,4
because
M
mapped to it has Type
S


One way to do it would be to write a inner query. Something like :-

SELECT T.id
FROM table1 T
JOIN table2 M
ON M.t_id = T.id
WHERE T.id NOT IN (SELECT m2.t_id
FROM table2 m2
WHERE m2.type = 'S')


But inner query can be very expensive as my table M has millions of rows. Is there a better way to do this ?

Answer

Use a conditional COUNT

SELECT T.id 
FROM   table1 T 
JOIN   table2 M 
  ON   M.t_id = T.id 
GROUP BY T.id 
HAVING COUNT( CASE WHEN M.Type = 'S' THEN 1 END ) = 0

Mean you dont have 'S' in that group.