Extract details from parent or child Where/IF they exist

Question

Hi Guys,

Hope someone can help.

Working with large datasets, I have 3 CTE temp tables.

Using multiple JOINS I have derived [Vessel_lD] and [Vessel_RepairTypeID] columns in a [VESSELS] CTE

Using columns [Vessel_lD] and [Vessel_RepairTypeID] from VESSELS CTE, I would like to find the matched rows in REPAIRS table first
If no match is found in there only then would like to see if I can find a match in the REPAIR_ARCHIVE Table.

FOR [VESSELS] & [REPAIR] - Match condition is ON [VESSELS]. [Vessel_lD] = [REPAIR]. [Current_RepairTypeID]
FOR [VESSELS] & [REPAIR_ARCHIVE] - Match condition is ON [VESSELS]. [Vessel_lD] = [REPAIR_ARCHIVE]. [Current_RepairTypeID]

Output am attempting to produce is VESSELS CTE LEFT JOIN with match data from either REPAIR or REPAIR_ARCHIVE result sets.

Here are the 3 tables:

[VESSELS] Table

Vessel_lD Vessel_RepairTypeID
13511443 79020921
13511443 79020921
13511443 79020921
13511443 79020921
13551645 69539505
13551645 69539505
14359108 96688504
14359108 96688504

REPAIR Table

Vessel_ID RepairDesciption Current_RepairTypeID Current_RepairDate
13511443 Engine 79020921 2018-11-21 13:33:14.477
13551645 Electrical 69539505 2018-01-08 14:30:30.977
14359108 Labour 96688505 2020-04-04 12:48:31.420

REPAIR_ARCHIVE Table
Vessel_ID RepairDesciption Current_RepairTypeID Next_RepairTypeID Current_RepairDate Next_RepairDate
13511443 Engine 51628561 79020921 2016-06-29 15:07:28.193 2016-06-29 15:07:28.193
13551645 Electrical 51802159 69539505 2016-07-07 11:34:19.837 2016-07-07 11:34:19.837
14359108 Labour 96688504 96688505 2020-04-03 12:48:31.420 2020-04-04 12:48:31.420
14359108 Labour 60345802 96688504 2020-02-06 14:57:09.243 2020-02-06 14:57:09.243
14359108 Labour 60345802 60345802 2017-05-24 07:58:52.867 2017-05-24 07:58:52.867

--Table 1  
DECLARE @VESSELS TABLE (  
Vessel_ID int NULL,  
Vessel_RepairTypeID int NULL  
)  
  
--Table 2  
DECLARE @REPAIR TABLE (  
Vessel_ID int NULL ,  
RepairDescription Varchar(50) NULL,  
Current_RepairTypeID int NULL ,  
Current_RepairDate DateTime NULL   
)  
  
--Table 3  
DECLARE @REPAIR_ARCHIVE TABLE (  
Vessel_ID int NULL ,  
RepairDescription Varchar(50) NULL,  
Current_RepairTypeID int NULL ,  
Next_RepairTypeID int NULL ,  
Current_RepairDate DateTime NULL,  
Next_RepairDate DateTime NULL   
)  
  
INSERT INTO @VESSELS (Vessel_ID , Vessel_RepairTypeID )  
VALUES   
(13511443,	79020921),  
(13511443,	79020921),  
(13511443,	79020921),  
(13511443,	79020921),  
(13551645,	69539505),  
(13551645,	69539505),  
(14359108,	96688504),  
(14359108,	96688504)  
  
INSERT INTO @REPAIR  (Vessel_ID , RepairDescription, Current_RepairTypeID,Current_RepairDate )  
VALUES   
(13511443,	'Engine',	79020921	 , cast('2018-11-21 13:33:14.477' as Datetime)),  
(13551645,	'Electrical',	69539505 ,	cast('2018-01-08 14:30:30.977' as Datetime)),  
(14359108,	'Labour',	96688505	, cast('2020-04-04 12:48:31.420' as Datetime))  
  
  
INSERT INTO @REPAIR_ARCHIVE  (Vessel_ID , RepairDescription, Current_RepairTypeID,	Next_RepairTypeID,	Current_RepairDate,	Next_RepairDate )  
VALUES   
(13511443,	'Engine',	51628561,	79020921,	cast('2016-06-29 15:07:28.193' as Datetime)	,cast('2016-06-29 15:07:28.193' as Datetime)	),  
(13551645,	'Electrical',	51802159,	69539505,	cast('2016-07-07 11:34:19.837' as Datetime)	,cast('2016-07-07 11:34:19.837' as Datetime)	),  
(14359108,	'Labour',	96688504,	96688505,	cast('2020-04-03 12:48:31.420' as Datetime)	,cast('2020-04-04 12:48:31.420' as Datetime)	),  
(14359108,	'Labour',	60345802,	96688504,	cast('2020-02-06 14:57:09.243' as Datetime)	,cast('2020-02-06 14:57:09.243' as Datetime)	),  
(14359108,	'Labour',	60345802,	60345802,	cast('2017-05-24 07:58:52.867' as Datetime)	,cast('2017-05-24 07:58:52.867' as Datetime)	)  
  
Select * from @VESSELS  
Select * from @REPAIR  
Select * from @REPAIR_ARCHIVE  
  
  

Answer 1

Hi @SQL Baby ,

Welcome to the microsoft TSQL Q&A forum!

Please try:

SELECT  v.Vessel_ID,v.Vessel_RepairTypeID,r.Vessel_ID,r.RepairDescription,  
r.Current_RepairTypeID,NULL AS Next_RepairTypeID,r.Current_RepairDate,  
NULL AS Next_RepairDate  
FROM @VESSELS v  
JOIN @REPAIR r  
ON v.Vessel_RepairTypeID=r.Current_RepairTypeID  
UNION ALL  
SELECT  *  
FROM @VESSELS v  
JOIN @REPAIR_ARCHIVE ra  
ON v.Vessel_RepairTypeID=ra. Current_RepairTypeID  

Output:

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Appreciate the response Echo.

Am trying do something like this - not my design, something i inherited and trying to make it work.

 /*
 Hi Echo,

 The two columns you can see in the @VESSELS result set are derived via mutiple JOINS within CTEs
 Am trying to produce the Expected Output via a CTE structured like this:

 */

 ;WITH Vessel_RepairDetails_CTE AS(
                            -- JOINS to Extracting Client vessels to repot on
                         ),

            Find_VesselsIDs_CTE AS (

                        /*
                        -- JOINS to indetify columns [Vessel_ID] &  [Vessel_RepairTypeID]
                        -- i.e. the @VESSELS ResulSet
                        -- Use these two columns [Vessel_ID] &  [Vessel_RepairTypeID] to check for  macthes in @REPAIR table 1st and 
                        -- if no match found then the @REPAIR_ARCHIVE table

                        */


                         LEFT JOIN

                         -- I would like to use [Vessel_ID] &  [Vessel_RepairTypeID] columns to
                        -- Check @REPAIR and @REPAIR_ARCHIVE to the RHS of LEFT JOIN

                         (
                           -- Look for repair details in REPAIR tbl first
                            -- If no match is found in there only then would like to see if I can find a match in the REPAIR_ARCHIVE Table.
                           /*

                            FOR [VESSELS]  & [REPAIR] -  Match condition is ON [VESSELS].[Vessel_lD] = [REPAIR]. [Current_RepairTypeID]
                            FOR [VESSELS]  & [REPAIR_ARCHIVE] - Match condition is ON     [VESSELS]. [Vessel_lD] = [REPAIR_ARCHIVE]. [Current_RepairTypeID]

                           */
                         )
                    )
                    Select * from Vessel_RepairDetails_CTE  -- this should give me the expected ouput