mysql - Setting a default value for JOINS on multiple tables with possible empty resultsets? -

so i'm trying join on multiple same id , each table may or may not have entry id.

select a.value, b.value, c.value, d.value tbl_a  join tbl_b b on a.id=b.id  join tbl_c c on a.id=c.id join tbl_d d on a.id=d.id a.id=123

obviously failing because if tbl_a doesn't have doesn't have entry, returns empty resultset , joins fail.

i've tried sorts of left joins, outer joins , couldn't work. i've tried setting clause like: where a.id=123 or b.id=123 or ... didn't work either.

i tried ugly union gives output in separate row.

select count(*), "a", ifnull(a.value,0) tbl_a a.id=123 union select count(*), "b", ifnull(b.value,0) tbl_b b b.id=123 union etc...

any ideas?

add other 3 id fields where clause or, outer joins.

shell