CODEWITHSUNDEEP
sqlsql-serverleft-join
user3817601
user3817601

Reputation: 13

How do you multiple left join the same table from 2 different tables in the same query?

I am in the process of refactoring some old SQL Server stored procs that are utilizing the *= operator instead of the preferred ANSI LEFT JOIN syntax. One of the procs I'm working on is giving me trouble because it's trying to left join the same table from 2 different tables in the same FROM clause and it's not returning the same results as the original old procedure returned.

I've refactored about 20 other stored procs that I have but none of them do multiple left joins like this on the same table. The old *= operator seems to have simplified this one. As you can see it was pretty straightforward to join on table3 all in the same where clause.

OLD CODE

SELECT ... TABLE3.column10

FROM TABLE1, TABLE2, TABLE3
WHERE TABLE1.column5 *= TABLE3.column5
and TABLE2.column8 *= TABLE3.column8

NEW CODE

SELECT
TABLE3.colum10

FROM
TABLE1
LEFT JOIN TABLE3 T3a
ON TABLE1.column5 = T3a.column5,

TABLE2
LEFT JOIN TABLE3 T3b
ON TABLE2.column8 = T3b.column8

Now since I have to alias TABLE3 in the new from clause, I don't get the expected results in my select of column10.

I'm hoping by looking at the old and new query you can kinda see what I'm shooting for. I can provide more detail if necessary but I am trying to keep my question as simple as possible at first.

Upvotes: 0

Views: 550

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269643

Why aren't you doing this?

SELECT t3.colum10 
FROM TABLE1 t1 CROSS JOIN
     TABLE2 t2 LEFT JOIN
     TABLE3 T3
     ON t1.column5 = T3a.column5 AND
        t2.column8 = T3b.column8;

Actually, I get a little confused with the old syntax, but I think you want:

SELECT t3.colum10 
FROM TABLE3 t3 LEFT JOIN
     TABLE1 t1
     ON t1.column5 = T3.column5 LEFT JOIN
     TABLE2 t2
     ON t2.column8 = T3.column8;

Or at least this logic makes more sense to me, regarding the three tables. However, the joins are pretty superfluous here.

Upvotes: 1

Related Questions