CODEWITHSUNDEEP
mysqlsql
dojogeorge
dojogeorge

Reputation: 1704

SQL Select where NOT matching specific selection

I have been using the following SQL:

SELECT DISTINCT NAME
FROM Events t1
LEFT JOIN UserHistory t2 ON t1.Name = t2.Event
WHERE t2.Event IS NULL

To select all rows from table 1 where table 2 is Null. This effectively filters out all my Table 1 data where Table 2 has data. However, I want to apply this only when a column in table 2 equals a certain value. Therefore I am looking to do a SELECT * FROM t2 WHERE t2.ID = 1 but am unsure how this fits into this query.

Upvotes: 1

Views: 1096

Answers (3)

valex
valex

Reputation: 24134

Also you can try query with NOT EXISTS:

SELECT DISTINCT NAME
FROM Events t1
WHERE NOT EXISTS(SELECT * FROM UserHistory t2 
                          WHERE t1.Name = t2.Event AND t2.ID = 1)

Upvotes: 2

GarethD
GarethD

Reputation: 69749

You need to add the predicate to the JOIN condition:

SELECT DISTINCT NAME
FROM Events t1
LEFT JOIN UserHistory t2 ON t1.Name = t2.Event AND t2.ID = 1
WHERE t2.Event IS NULL;

If you add it to the WHERE you effectively turn your outer join into an inner join, meaning no rows will be returned (since NULL = 1 evaluates to false)

Upvotes: 1

juergen d
juergen d

Reputation: 204746

SELECT DISTINCT NAME
FROM Events t1
LEFT JOIN UserHistory t2 ON t1.Name = t2.Event and t2.certain_column = 1234
WHERE t2.Event IS NULL

Upvotes: 3

Related Questions