Multiple Left Joins on Multiple Tables in One Query

Multiple left joins on multiple tables in one query

This kind of query should work - after rewriting with explicit JOIN syntax:

SELECT something
FROM   master      parent
JOIN   master      child ON child.parent_id = parent.id
LEFT   JOIN second parentdata ON parentdata.id = parent.secondary_id
LEFT   JOIN second childdata ON childdata.id = child.secondary_id
WHERE  parent.parent_id = 'rootID';

The tripping wire here is that an explicit JOIN binds before a comma (,), which is otherwise equivalent to CROSS JOIN. The manual here:

In any case JOIN binds more tightly than the commas separating
FROM-list items.

After rewriting the first, all joins are applied left-to-right (logically - Postgres is free to rearrange tables in the query plan otherwise) and it works.

Just to make my point, this would work, too:

SELECT something
FROM   master parent
LEFT   JOIN second parentdata ON parentdata.id = parent.secondary_id
,      master child
LEFT   JOIN second childdata  ON childdata.id = child.secondary_id
WHERE  child.parent_id = parent.id
AND    parent.parent_id = 'rootID';

But explicit JOIN syntax is generally clearer.

And be aware that multiple (LEFT) JOIN can multiply rows:

• Two SQL LEFT JOINS produce incorrect result

Multiple LEFT JOIN in multiple tables

When looking for aggregates from different tables, you should always aggregate before joining:

SELECT 
  s.*,
  mul.multimedia,
  cat.categories 
FROM sections s 
LEFT JOIN
(
  SELECT
    sm.section_id,
    GROUP_CONCAT(CONCAT_WS(':', m.type, m.id, m.filename) SEPARATOR ',') AS multimedia
  FROM sections_multimedia sm
  JOIN multimedia m ON sm.multimedia_id = m.id 
  GROUP BY sm.section_id
) mul ON s.id = mul.section_id
LEFT JOIN
(
  SELECT
    sc.section_id,
    GROUP_CONCAT(CONCAT_WS(':', c.id) SEPARATOR ',') AS categories
  FROM sections_categories sc
  JOIN categories c ON sc.category_id = c.id
  GROUP BY sc.section_id
) cat ON s.id = cat.section_id;

Multiple LEFT OUTER JOIN on multiple tables

The join on D is an inner join, the rest are left outer joins:

SELECT *
FROM TABLEA A JOIN
     TABLED D
     ON D.Z = A.Z LEFT JOIN
     TABLEB B
     ON A.X = B.X LEFT JOIN
     TABLEC C
     ON B.Y = C.Y 
WHERE MY_COL = @col_val;

I always start chains of joins with inner joins followed by the left outer join. I never use right join, and full join rather rarely. The inner joins define the rows in the result set, so they come first.

SQL Multiple Left Joins on Same Parent Key

You would use union all:

select a.id, a.name, b.title, a.amount
from TableA a Left Join
     TableB b
     on a.ID = b.ID 
union all
Select a.id, a.name, b.title, a.amount
from TableA a Left Join
     TableC c
     on a.ID = c.ID and a.name = c.name

Multiple LEFT JOINS for the same column

If you're LEFT joining the same table twice, could you just join on it once and add an OR to your join and use both conditions?

SELECT a.*, b.Game_Code
  FROM [NCAAF].[dbo].[MasterSheet] a
  LEFT JOIN (SELECT DISTINCT Game_Code, Date, Visit_Team_Code, Home_Team_Code
  from dbo.game2018) b
  ON a.Date = b.Date and 
((a.[Team Code] = b.Visit_Team_Code and a.[Opponent Code] = b.Home_Team_Code)
OR
(a.[Team Code] = b.Home_Team_Code and a.[Opponent Code] = b.Visit_Team_Code))

Combine multiple left joins in 1 query

This should bring back the rows for both tables B and C for each row of table A:

SELECT A.BIZ_GROUP,
       A.ORDER_ID,
       A.STATION,
       A.TC_DATE,
       A.WANT_DATE,
       A.TIME_SLOT,
       A.JOB_CODE,
       A.[ADDRESS],
       A.CITY,
       A.TECH_ID,
       A.PREMISE,
       ISNULL(B.LAST_ARRIVED, A.LAST_ARRIVE) AS ARRIVED,
       A.ORDER_CLOSED,
       A.COMP_STATUS,
       A.WORK_STATUS,
       A.REMARKS,
       A.CORRECTION,
       C.*
FROM MET_timecommit A
     LEFT JOIN(SELECT premise,
                      TC_DATE,
                      TECH_ID,
                      MIN(last_arrive) AS LAST_ARRIVED
               FROM MET_timecommit
               WHERE PREMISE IS NOT NULL
               GROUP BY premise,
                        TC_DATE,
                        TECH_ID) B ON B.TC_DATE = A.TC_DATE
                                  AND B.PREMISE = A.PREMISE
     LEFT JOIN (SELECT ORDER_ID,
                       created,
                       host_creation,
                       went_to 
                FROM workload
                WHERE went_to >= getdate()-365) C ON C.went_to=A.MET_timecommit.TC_DATE
                                                 AND C.order_id=A.MET_timecommit.order_id

Avoiding multiple left joins of a single table for multiple columns

You can also use aggregation:

select t1.*, t2.code_01, t2.code_02, t2.code_03
from table1 t1 left join table2
     (select t2.special_id,
             max(case when t2.dependent_value = '01' then t2.value_wanted end) as code_01,
             max(case when t2.dependent_value = '02' then t2.value_wanted end) as code_02,
             max(case when t2.dependent_value = '03' then t2.value_wanted end) as code_03
      from table2 t2
      group by t2.special_id
     ) t2
     on t2.special_id = t1.special_id ;

Instead of a new join, you need to add a new condition to the subquery. Whether this is faster or slower . . . well that depends.

---

Related Topics