CODEWITHSUNDEEP
sql-server
Symone Coleman
Symone Coleman

Reputation: 49

Combine multiple left joins in 1 query

I have two queries that I would like to combine. One query is left joining columns in the same table, the other query is left joining columns from two different tables. Both queries have the same table, just unsure how to properly set up the query.

1st Query:

SELECT BIZ_GROUP,
       ORDER_ID,
       STATION,
       A.TC_DATE,
       WANT_DATE,
       TIME_SLOT,
       JOB_CODE,
       [ADDRESS],
       CITY,
       A.TECH_ID,
       A.PREMISE,
       ISNULL(B.LAST_ARRIVED, A.LAST_ARRIVE) AS ARRIVED,
       ORDER_CLOSED,
       COMP_STATUS,
       WORK_STATUS,
       REMARKS,
       CORRECTION
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

2nd query:

SELECT *
FROM MET_timecommit
     LEFT JOIN (SELECT ORDER_ID,
                       created,
                       host_creation,
                       went_to 
                FROM workload
                WHERE went_to >= getdate()-365) C ON C.went_to=MET_timecommit.TC_DATE
                                                 AND C.order_id=MET_timecommit.order_id

Evidently I am not used to this forum. You all don't have to be so rude. TDP was able to help me out based on what I provided. All other comments were unnecessary.

Upvotes: 2

Views: 1151

Answers (1)

TDP
TDP

Reputation: 1231

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

Upvotes: 2

Related Questions