SQL Combining multiple variations of select statement with joins?

Omega Mike 21 Reputation points
2022-05-02T19:40:21.957+00:00

I have several .sql files with a similar query to the one below that I have to run separately then combine in an excel sheet running formulas to generate the final desired result. It's time consuming but the best I can do being self-taught and only functioning as a developer occasionally. It's time I streamline this. I'm wondering if there's a much cleaner/simpler way to combine these multiple statements while keeping them easy to manage and add onto as things progress.

Query #1: 

IF OBJECT_ID('tempdb.dbo.#tempResult', 'U') IS NOT NULL
  DROP TABLE #tempResult; 

SELECT distinct CH4A.[vehicle_make.name_en], CH4A.[vehicle_model.name_en], CH4A.[vehicle_short.vehicle_year]
into #tempResult
from dbo.omegalink_raw CH4A
Join dbo.omegalink_raw CH4B ON CH4A.[vehicle.vehicle_id] = CH4B.[vehicle.vehicle_id]
Join dbo.omegalink_raw CH4C ON CH4A.[vehicle.vehicle_id] = CH4C.[vehicle.vehicle_id]
WHERE CH4A.[firmware_family.name] = 'DL-CH4'AND CH4B.[ads-hrn_sku] LIKE '%CH4%'
GROUP BY  CH4A.[vehicle.vehicle_id],  CH4A.[vehicle_make.name_en],  CH4A.[vehicle_model.name_en], CH4A.[vehicle_short.vehicle_year], CH4A.submodel, CH4A.[firmware_family.name]

SELECT distinct CH4A.[vehicle.vehicle_id], CH4A.[vehicle_make.name_en], CH4A.[vehicle_model.name_en], CH4A.submodel, CH4A.[vehicle_short.vehicle_year], CH4A.[firmware_family.name], 'OL-HRN-RS-CH4' AS [ol-hrn-rs_sku]
from dbo.omegalink_raw CH4A
Join dbo.omegalink_raw CH4B ON CH4A.[vehicle.vehicle_id] = CH4B.[vehicle.vehicle_id]
Join dbo.omegalink_raw CH4C ON CH4A.[vehicle.vehicle_id] = CH4C.[vehicle.vehicle_id]
Join #tempResult tr on CH4A.[vehicle_make.name_en] = tr.[vehicle_make.name_en] and CH4A.[vehicle_model.name_en] = tr.[vehicle_model.name_en] and CH4A.[vehicle_short.vehicle_year] = tr.[vehicle_short.vehicle_year]
WHERE (CH4A.[firmware_family.name] = 'DL-CH4' AND CH4C.submodel LIKE '%manual%')
OR ( CH4A.[firmware_family.name] = 'DL-CH4'AND CH4B.[ads-hrn_sku] LIKE '%CH4%') 
GROUP BY  CH4A.[vehicle.vehicle_id],  CH4A.[vehicle_make.name_en],  CH4A.[vehicle_model.name_en], CH4A.[vehicle_short.vehicle_year], CH4A.submodel, CH4A.[firmware_family.name]
ORDER BY [vehicle_make.name_en], [vehicle_model.name_en], submodel, [vehicle_short.vehicle_year] DESC

Query #2: 

IF OBJECT_ID('tempdb.dbo.#tempResult', 'U') IS NOT NULL
  DROP TABLE #tempResult; 

SELECT distinct OL1.[vehicle_make.name_en], OL1.[vehicle_model.name_en], OL1.[vehicle_short.vehicle_year]
into #tempResult
from dbo.omegalink_raw OL1
Join dbo.omegalink_raw OL2 ON OL1.[vehicle.vehicle_id] = OL2.[vehicle.vehicle_id]
Join dbo.omegalink_raw OL3 ON OL1.[vehicle.vehicle_id] = OL3.[vehicle.vehicle_id]
WHERE (oL1.[firmware_family.name] = 'DL-GM12'AND OL2.[ads-hrn_sku] LIKE '%GM12%') OR (oL1.[firmware_family.name] = 'DL-GM7'AND OL2.[ads-hrn_sku] LIKE '%GM12%')
GROUP BY  OL1.[vehicle.vehicle_id],  OL1.[vehicle_make.name_en],  OL1.[vehicle_model.name_en], OL1.[vehicle_short.vehicle_year], OL1.submodel, OL1.[firmware_family.name]

SELECT distinct OL1.[vehicle.vehicle_id], OL1.[vehicle_make.name_en], OL1.[vehicle_model.name_en], OL1.submodel, OL1.[vehicle_short.vehicle_year], OL1.[firmware_family.name], 'OL-HRN-RS-GM12' AS [ol-hrn-rs_sku]
from dbo.omegalink_raw OL1
Join dbo.omegalink_raw OL2 ON OL1.[vehicle.vehicle_id] = OL2.[vehicle.vehicle_id]
Join dbo.omegalink_raw OL3 ON OL1.[vehicle.vehicle_id] = OL3.[vehicle.vehicle_id]
Join #tempResult tr on OL1.[vehicle_make.name_en] = tr.[vehicle_make.name_en] and OL1.[vehicle_model.name_en] = tr.[vehicle_model.name_en] and OL1.[vehicle_short.vehicle_year] = tr.[vehicle_short.vehicle_year]
WHERE (OL1.[firmware_family.name] = 'DL-GM12' AND OL3.submodel LIKE '%manual%')
OR (oL1.[firmware_family.name] = 'DL-GM12'AND OL2.[ads-hrn_sku] LIKE '%GM12%')
OR (oL1.[firmware_family.name] = 'DL-GM7'AND OL2.[ads-hrn_sku] LIKE '%GM12%') 
OR (OL1.[firmware_family.name] = 'DL-GM7' AND OL3.submodel LIKE '%manual%')
GROUP BY  OL1.[vehicle.vehicle_id],  OL1.[vehicle_make.name_en],  OL1.[vehicle_model.name_en], OL1.[vehicle_short.vehicle_year], OL1.submodel, OL1.[firmware_family.name]
ORDER BY [vehicle_make.name_en], [vehicle_model.name_en], submodel, [vehicle_short.vehicle_year] DESC

Thanks for any suggestions or pointers!

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,545 questions
0 comments
Accepted answer
  1. Naomi 7,361 Reputation points
    2022-05-03T14:05:09.227+00:00

    I think you're talking about UNION or UNION ALL if you want to combine results of the queries into one set of data.

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Bert Zhou-msft 3,421 Reputation points
    2022-05-03T01:25:03.787+00:00

    Hi，@Omega Mike

    Welcome to Microsoft T-SQL Q&A Forum!

    Have you heard of cte？ You can put repeated code into general expressions , and then when querying below , you only need to call the result of cte to perform multi-table join links .
    You can also define a variable to receive the repeated value . For example , if 'DL-GM12' appears repeatedly here , then we can declare @var1='DL-GM12' , so that the value of the on connection can be replaced.

    Like This： 

    DECLARE @v1 VARCHAR(40),@v2 VARCHAR(40),@v3 VARCHAR(40)  ;  
SET @v1 = 'DL-CH4';  
SET @v2 = '%CH4% '  
SET @v3 = '%manual%'  
  
;With cte as  
(  
	select distinct CH4A.[vehicle_make.name_en], CH4A.[vehicle_model.name_en], CH4A.[vehicle_short.vehicle_year]  
    into #tempResult  
    from dbo.omegalink_raw CH4A  
	from dbo.omegalink_raw CH4A   
	Inner Join dbo.omegalink_raw CH4B ON CH4A.[vehicle.vehicle_id] = CH4B.[vehicle.vehicle_id]  
    Inner Join dbo.omegalink_raw CH4C ON CH4A.[vehicle.vehicle_id] = CH4C.[vehicle.vehicle_id]  
	WHERE CH4A.[firmware_family.name] = @v1 AND CH4B.[ads-hrn_sku] LIKE @v2  
	GROUP BY  CH4A.[vehicle.vehicle_id],  CH4A.[vehicle_make.name_en],  CH4A.[vehicle_model.name_en], CH4A.[vehicle_short.vehicle_year], CH4A.submodel, CH4A.[firmware_family.name]  
)  
Select * from cte  Inner Join #tempResult tr on cte.[vehicle_make.name_en] = tr.[vehicle_make.name_en]   
and cte.[vehicle_model.name_en] = tr.[vehicle_model.name_en] and cte.[vehicle_short.vehicle_year] = tr.[vehicle_short.vehicle_year]  
WHERE (cte.[firmware_family.name] = @v1  AND cte.submodel LIKE @v3)  
ORDER BY [vehicle_make.name_en], [vehicle_model.name_en], submodel, [vehicle_short.vehicle_year] DESC

    Best regards,
    Bert Zhou

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    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.