CODEWITHSUNDEEP
mysql
Xaver
Xaver

Reputation: 11652

MySQL left join with NULL values

I'm certain this has been asked and answered already but don't know how exactly the question should be.

I have two tables

ID | name
=========
 1 | foo
 2 | bar
 3 | lou
 4 | sue

and a meta table:

p_ID | key | value
===================
  1  | poo | 1
  2  | zoo | 'whatever'
  3  | clu | 423
  4  | poo | 1

I like to get all entries from the first table which doesn'T have a poo value assigned:

ID | name
=========
 2 | bar
 3 | lou

My approach was

SELECT * 
  FROM table AS p 
  LEFT JOIN meta AS m 
    ON m.p_id = p.ID 
 WHERE m.key = 'poo' 
   AND m.value IS NULL

but this returns an empty result

Upvotes: 2

Views: 2607

Answers (3)

Martin Schneider
Martin Schneider

Reputation: 3268

You have to move the m.key = 'poo' expression into the ON-clause. Everything that is in the WHERE-clause MUST be present, even in a LEFT JOIN.

SELECT * 
  FROM table AS p 
  LEFT 
  JOIN meta AS m 
    ON m.p_id = p.ID 
   AND m.key = 'poo'
 WHERE m.value IS NULL

Upvotes: 4

Blank
Blank

Reputation: 12378

I think you should want this;)

SELECT *
FROM table AS p
LEFT JOIN meta AS m
ON m.p_id = p.ID
AND m.key = 'poo'
WHERE m.value IS NULL

When you use m.key = 'poo' in WHERE clause, this will be computed as INNER JOIN, so you only get records with m.key = 'poo', not all rows in table.

Upvotes: 1

KaeL
KaeL

Reputation: 3659

You can use INNER JOIN instead.

SELECT *
    FROM `table` T
    INNER JOIN meta M ON M.p_id = T.ID
    WHERE M.key <> 'poo'

Upvotes: 0

Related Questions