MySQL left join with NULL values

Question

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

Strawberry · Accepted Answer · 2016-07-05 07:16:18Z

4

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

edited Jul 5, 2016 at 7:16

Strawberry

34k14 gold badges43 silver badges57 bronze badges

answered Jul 5, 2016 at 7:11

Martin Schneider

3,2784 gold badges21 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Xaver Over a year ago

I knew it was that easy!

Blank · Accepted Answer · 2016-07-05 07:11:30Z

1

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.

answered Jul 5, 2016 at 7:11

Blank

12.4k1 gold badge16 silver badges34 bronze badges

Comments

KaeL · Accepted Answer · 2016-07-05 07:10:22Z

0

You can use INNER JOIN instead.

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

answered Jul 5, 2016 at 7:10

KaeL

3,6612 gold badges31 silver badges59 bronze badges

Collectives™ on Stack Overflow

MySQL left join with NULL values

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related