I think you're talking about UNION or UNION ALL if you want to combine results of the queries into one set of data.
SQL Combining multiple variations of select statement with joins?
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!
1 additional answer
Sort by: Most helpful
-
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.