JOIN on column only if NOT NULL
if you want same result as you have in first query - you must make left join only with table a, like this :
SELECT ...
FROM b, c
LEFT JOIN a
ON b.id = a.b_id and b.id = a.b_id
WHERE
c.id = ... b.c_id
or if you want the same style with all tables, you can use inner join with table b, like this :
SELECT ...
FROM c
INNER JOIN b
on b.c_id = c.id
LEFT JOIN a
ON b.id = a.b_id
WHERE
c.id = ...
in my both query we select data from table b where column is not null
knittl
#SOreadytohelp web: php, html, css, js programming: c(|++|#), java, python graphics: inkscape, blender, gimp vcs: git, hg, bzr, svn vi-user If my answers (or questions) were helpful to you and you are experimenting with bitcoins, please consider sending me a small amount of milli-bitcoins: 1C4up92fVvPai7d7W2J4evFnDSiWBZNQCV
Updated on June 05, 2022Comments
-
knittl almost 2 years
I'm in the process of re-writing an old SQL query and have troubles making sense out of it. It contains several conditions of the form
SELECT ... FROM a, b, c WHERE c.id = ... AND ( a.x_id IS NULL OR a.x_id = c.x_id ) AND b.id = a.b_id (+)
Can this query be rewritten using proper JOIN syntax? Is it equivalent to the following or will it produce different results under certain circumstances?
SELECT ... FROM b LEFT JOIN a ON b.id = a.b_id LEFT JOIN c ON a.x_id = c.x_id WHERE c.id = ...
The original query is 100 lines long and spans 5 tables, plus several joins over "virtual tables" (i.e. where conditions of the form
x.z_id = y.z_id
), which makes it hard to break down into more manageable bits or debug.