eric eric - 6 months ago 7
SQL Question

how can I apply a condition to just one part of a multiple join in SQL? (SOLVED)

My example query is:

SELECT
tab1.col1, tab2.col1, tab3.col2
FROM
tab1 JOIN tab2 ON tab1.col1=tab2.col1
left JOIN tab3 ON tab1.col2=tab3.col2
WHERE blah blah GROUP BY blah blah HAVING blah blah;


I want to apply a condition (such as a
"where" or "having"
to just one of the joins but not the other. How do I do so?

Something like
"SELECT ___, ___, ___ FROM tab1 JOIN tab2 ON ___ WHERE ___ JOIN tab3 ON ___ WHERE ___ GROUP BY ___"
is impossible it seems.

Thanks for your help.

Answer

There are multiple ways to achieve that

First would be to simply put the filter clause at the end of the query. Mostly this must produce same results, although in some cases, this might not be the case.

SELECT
        tab1.col1, tab2.col1, tab3.col2
FROM
        tab1
JOIN
        tab2
        ON tab1.col1=tab2.col1
LEFT JOIN
        tab3
        ON tab1.col2=tab3.col2
WHERE
        blah blah
        AND
        desired_column=filter_value
GROUP BY
        blah blah
HAVING
        blah blah
;

Second option is to use nested queries. It is more likely that this method with produce the results of your liking. An intelligent query optimizer may rewrite this query to the one above or vice versa.

SELECT
        tab1.col1, tab2.col1, tab3.col2
FROM
        tab1
JOIN
        (SELECT col1, col2 FROM tab2 WHERE desired_column=filter_value) tab2
        ON tab1.col1=tab2.col1
LEFT JOIN
        tab3
        ON tab1.col2=tab3.col2
WHERE
        blah blah
GROUP BY
        blah blah
HAVING
        blah blah
;

A third option, in case you are using INNER JOIN, is to mention this as part on join condition

SELECT
        tab1.col1, tab2.col1, tab3.col2
FROM
        tab1
JOIN
        tab2
        ON tab1.col1=tab2.col1
        AND
        desired_column=filter_value
LEFT JOIN
        tab3
        ON tab1.col2=tab3.col2
WHERE
        blah blah
GROUP BY
        blah blah
HAVING
        blah blah
;

You may also use the last one with OUTER JOINs if you do not want to filter the set and only relate based upon the mentioned criteria.