question

OmegaMike-9452 avatar image
0 Votes"
OmegaMike-9452 asked OmegaMike-9452 commented

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!

sql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

NaomiNNN avatar image
0 Votes"
NaomiNNN answered OmegaMike-9452 commented

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

· 8
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks, I'm working on the UNION syntax now then will see if CTE helps clean it up a bit. :)

0 Votes 0 ·

Before you dive too deep into this... I compared the two queries you posted, and I made a few observations.

The queries seems to be quite similar, all that is different is the conditions that the contents of the last column [ol-hrn-rs_sku]. Does this observation hold for the other queries too?

Furthermore, I see room for simplifications. You are only reading one single table, omegalink_raw, but in a fashion that makes my head spinning. There is certainly no need for the triple self-joins, and I am not sure that I see the point with the temp table.

I'm thinking that it is not impossible that this could be reduced to a single query, but we need to know more to investigate this.

0 Votes 0 ·

Thanks for the notes and taking the time, I really appreciate it. I'm self taught and only work in SQL a few times a year. I'm not surprised if there's a more idealistic/efficient way to do this. When I originally developed this a couple of yrs ago, I tried more elegant solutions that just wouldn't work for me and ran out of time. This is used to generate a report once a month and works so I stuck with it. haha. Ok, just a few points about your questions:

  • Yes, your observation holds for the other queries mostly. They are very much the same but a handful have additional/unique conditions (still very similar in operation - [column] LIKE or NOT LIKE "value", etc.)

  • The reason for the joins is because I need to manipulate the data to add the [ol-hrn-sku] value to other similar records with different conditions.

What I'm accomplishing:
- Add [ol-hrn-sku] to all vehicles that have a specific [firmware_family.name] and [ad-hrn-sku] - call this "RS1"
NOTE ABOUT RS1: This is inheritantly limited to vehicles with a particular [submodel] due to the original data given to me from another source.
- Then Add [ol-hrn-sku] to vehicles that match RS1 but with a different [submodel] - you will see this in the WHERE clause of my query.

I have been unable to accomplish this without self joins.

BTW, I was able to successfully combine my ~20 queries with UNION. Thank you. I had tried this some time ago but my mistake was not using UNION on just the temp table before using UNION on the conditional JOIN query.


0 Votes 0 ·

Oh, I forgot to address the temp table. I'm using it to store the vehicle info (make, model, year) without the [submodel] as an informal key to match it with other vehicles w/ different [submodel] containing specific values.

Plain(ish) english: I'm adding [ol-hrn-sku] to all vehicles with a certain [ads-hrn-sku] which all happen to be only with automatic transmissions (noted in [submodel]). Then I'm adding [ol-hrn-sku] to the same vehicles that have a manual transmission (differing [submodel] text). Btw, [submodel] is boolean, not standardized/structured data values.

0 Votes 0 ·
Show more comments
BertZhoumsft-7490 avatar image
0 Votes"
BertZhoumsft-7490 answered OmegaMike-9452 commented

Hi,@OmegaMike-9452

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.



· 6
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you, this is helpful. CTE is new to me and will surely help with cleaning up code and making each query (DL-CH4 / DL-GM12) quicker to replicate/modify. However, my main goal was how to combine the results of both queries for DL-CH4 & DL-GM12. And please note that the conditions vary between the 2 queries but most are shared. So, unfortunately, I can't apply one set of conditions to all instances (around a dozen and growing).

I'm hoping for a result like:

select "all DL-CH4 conditions with temp table joins, etc."
AND
select "all DL-GM12 conditions with temp table joins, etc."
AND
select "all DL-???? conditions with temp table joins, etc."

and so on. I hope this makes sense.

Thank you so much!

0 Votes 0 ·

Oh, also, I'm getting a "syntax error near keyword into" error having SELECT.... INTO.... within the CTE.

0 Votes 0 ·
NaomiNNN avatar image NaomiNNN OmegaMike-9452 ·

You cannot use INTO keyword inside the CTE, it has to be outside in the statement which uses CTE.

1 Vote 1 ·

Yeah, i read that on other searches. You included it on line 9 above, that's why I tried it. Thanks again!

0 Votes 0 ·
Show more comments