CODEWITHSUNDEEP
mysql
Caadi0
Caadi0

Reputation: 504

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 ?

Upvotes: 1

Views: 32

Answers (3)

Joel Gauvreau
Joel Gauvreau

Reputation: 3636

Not the prettiest but it seems to work

select T.ID 
from Table1 T 
left join Table2 M on M.T_ID = T.ID
group by T.Id
having sum(case when M.Type = 'S' then 1 else 0 end) = 0

You should check if it is actually less expensive in the execution plan.

Upvotes: 1

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

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.

Upvotes: 2

Frostidas Noman
Frostidas Noman

Reputation: 14

You should look into LEFT JOIN as opposed to INNER JOIN.

Upvotes: -2

Related Questions