R.JN R.JN - 6 months ago 8
SQL Question

How can I use a join for two columns corresponding to the same primary key?

I have a table with five columns:

streetId, streetName, areaId, ISfSectionId1, ISFsectionId2


where
areaId
is a foreign key for table
area
and
isfsectionid1
and
isfsectionid2
are foreign keys for the same primary key
isfsectionId
(isfsectionId2 can have null value).

I am using this query to join them

SELECT
s.streetId, s.streetName, a.areaName, i.isfsectionName, d.ISFsectionName
FROM
area a
INNER JOIN
street s ON s.areaId = a.areaId
INNER JOIN
ISFsections i ON s.fasileone = i.ISFsectionId
JOIN
ISFsections d ON s.fasiletwo = d.ISFsectionId


Without that last join, it is working fine, but when adding the last join, it's returning records for the ISFsectionId2 only.

What is the problem?

Answer
SELECT s.streetId
      ,s.streetName
      ,a.areaName
      ,i.isfsectionName
      ,d.ISFsectionName
from       area        a 
INNER join street      s on s.areaId = a.areaId 
LEFT join  ISFsections i on s.fasileone = i.ISFsectionId 
LEFT join  ISFsections d on s.fasiletwo = d.ISFsectionId