MySQL - Join as zero if record Not IN
- by Zurahn
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.
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?
