CODEWITHSUNDEEP
sqlsql-server
refresh
refresh

Reputation: 1329

Several left join on in a query

I have the following query:

select *
from House h
join Users u
on h.ID_COORD = u.ID
join PA_USERS createdBy
on h.USR_N_ID_CREATION = createdBy.ID
join Contacts c
on h.ConID= c.ID
left join Price p
on h.PriD = p.ID
join Car c
on h.CID = c.ID
left join Person p
on c.PID = p.ID
left join Assistance a
on c.AID = a.ID
left join Travel t
on p.SER_N_ID = t.SER_N_ID
left join Travel at
on a.SER_N_ID = at.SER_N_ID
where h.LEA_N_ID = 9132
and t.lang = 'en'
and p.lang = 'en'
and at.lang = 'en'

However because of the at.lang = 'en', the query is not returning me any data. I need a left join on both Assistance and Person and need the left join for both tables on the Travel table. Any idea of how to do this.

Upvotes: 0

Views: 58

Answers (4)

Thom A
Thom A

Reputation: 96038

Putting a clause in the WHERE without handling nullability from a LEFT JOIN will cause the join to implicitly become an INNER JOIN. Take the simple example below:

SELECT *
FROM TableA A
     LEFT JOIN TableB B ON A.PID = B.FID
WHERE B.[name] = 'John';

This turns causes the LEFT JOIN to implicitly become an INNER JOIN as the clause B.[name] = 'John' must be true for all rows (regardless of if a row in TableB was returned).

Normally, the solution is to therefore put the requirement in the ON clause:

SELECT *
FROM TableA A
     LEFT JOIN TableB B ON A.PID = B.FID
                       AND B.Name = 'John';

Thus this turns your query to:

SELECT *
FROM House h
     JOIN Users u ON h.ID_COORD = u.ID
     JOIN PA_USERS createdBy ON h.USR_N_ID_CREATION = createdBy.ID
     JOIN Contacts c ON h.ConID = c.ID
     LEFT JOIN Price p ON h.PriD = p.ID
     JOIN Car c ON h.CID = c.ID
     LEFT JOIN Person p ON c.PID = p.ID
                       AND p.lang = 'en'
     LEFT JOIN Assistance a ON c.AID = a.ID
     LEFT JOIN Travel t ON p.SER_N_ID = t.SER_N_ID
                       AND t.lang = 'en'
     LEFT JOIN Travel at ON a.SER_N_ID = at.SER_N_ID
                        AND at.lang = 'en'
WHERE h.LEA_N_ID = 9132;

Note that this won't fix your query anyway, as you have reused aliases. For example Person p and Price p. I haven't fixed these errors for you, as I suspect that we don't have the full query here (as the one in your question would never have run due to the alias reuse).

Upvotes: 3

Mazhar
Mazhar

Reputation: 3837

Move the AND at.lang = 'en' away from the WHERE clause and into the LEFT JOIN clause. Putting in the WHERE clause changes the LEFT JOIN to a INNER JOIN

SELECT  *
FROM
        House       h
JOIN
        Users       u ON h.ID_COORD                     = u.ID
JOIN
        PA_USERS    createdBy ON h.USR_N_ID_CREATION    = createdBy.ID
JOIN
        Contacts    c ON h.ConID                        = c.ID
LEFT JOIN
        Price       p ON h.PriD                         = p.ID
JOIN
        Car         c ON h.CID                          = c.ID
LEFT JOIN
        Person      p ON c.PID                          = p.ID
LEFT JOIN
        Assistance  a ON c.AID                          = a.ID
LEFT JOIN
        Travel      t ON p.SER_N_ID                     = t.SER_N_ID
LEFT JOIN
        Travel      at  ON a.SER_N_ID                   = at.SER_N_ID
                        AND at.lang         = 'en'
WHERE
    h.LEA_N_ID  = 9132
AND t.lang          = 'en'
AND p.lang          = 'en'
;

Upvotes: 1

George Menoutis
George Menoutis

Reputation: 7260

When using LEFT JOIN, an ON condition is not equivelant to a where condition, as the former will still get you rows, whereas the latter will not.

Remove this for your WHERE:

and at.lang = 'en'

and add it to the LEFT JOIN's ON:

left join Travel at
on a.SER_N_ID = at.SER_N_ID
and at.lang = 'en'

Upvotes: 1

Thorsten Kettner
Thorsten Kettner

Reputation: 95101

Outer join means: when there is no matching record add a pseudo record with all columns set to null.

So in case there is no matching travel record, t.lang will be null; it will not be 'en'.

Move your conditions to the ON clauses to make it work:

left join Travel t on p.SER_N_ID = t.SER_N_ID and t.lang = 'en'

etc.

Upvotes: 1

Related Questions