CODEWITHSUNDEEP
mysqljoin
Elle H
Elle H

Reputation: 12217

MySQL - Join as zero if record Not IN

To explain by example, take two tables, A and B

Table A
id  foo
 1   x
 2   y
 3   z


Table B
id  aid bar
 1   3   50
 2   1  100

An example join

SELECT foo, bar FROM a, b WHERE a.id = b.aid;

Garners a result of

foo  bar
 z    50
 x   100

What I would like to do is get all values of foo and for any instances where there isn't a corresponding bar value, return 0 for that column.

foo  bar
 z    50
 y     0
 x   100

My best guess was something along the lines of

SELECT foo, bar AS br FROM a, b 
WHERE a.id = b.aid 
OR a.id NOT IN (SELECT aid FROM b);

But that returns duplicates and non-zero values for bar.

Possible?

Upvotes: 1

Views: 328

Answers (2)

Ignacio Vazquez-Abrams
Ignacio Vazquez-Abrams

Reputation: 798784

SELECT a.foo, IFNULL(b.bar, 0) AS bar
FROM tablea AS a
LEFT JOIN tableb AS b
  ON a.id=b.aid

Upvotes: 2

newdayrising
newdayrising

Reputation: 3802

SELECT a.foo, COALESCE(b.bar, 0) as bar
FROM a
LEFT OUTER JOIN b ON a.id = b.aid

Upvotes: 5

Related Questions