Help with Joins

Question

Help with Joins

gwb

SSC Veteran

Points: 218
More actions
January 18, 2023 at 9:22 pm

#4139182
Hi there, I'm new to T-SQL and am struggling with the join concept in practice. I'd like to join the results of multiple tables to one generic column called "Codes". I have 7 different tables that use the same name, just have different codes depending on the table. Each row record has a unique identifier that is present on each table within the database that can be used to link up the different tables.
Table1.Codes
Table2.Codes
Table3.Codes
Table4.Codes
- This topic was modified 1 year, 10 months ago by gwb. Reason: Further clarification on linking
January 19, 2023 at 3:58 am

This was removed by the editor as SPAM

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 101098 More actions · Answer 1

Something like this. The key is using aliases (AS ...) for the tables to make the code easier to write and to follow:

SELECT t0.column1, t1.Description, t2.Description, ...
FROM Table0 AS t0
INNER JOIN Table1 AS t1 ON t1.Codes = t0.Code1
INNER JOIN Table2 AS t2 ON t2.Codes = t0.Code2
...

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Phil Parkin SSC Guru Points: 246960 More actions · Answer 2

You want to see all of the codes from all of the different tables in a single column?

For that, you will need a UNION query and no joins at all.

SELECT Code from T1
UNION ALL
SELECT Code from T2
etc

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

ratbak Ten Centuries Points: 1358 More actions · Answer 3

If all the tables are joined in the same query, then you need to alias Table0 in the join to each table to enabled SQL Server... and you... to resolve the joins -- e.g.,

SELECT ...
FROM Table1
INNER JOIN Table0 t1Codes ON t1Codes.Code = Table1.Codes
INNER JOIN Table2 ON...
INNER JOIN Table0 t1Codes ON t2Codes.Code = Table2.Codes
INNER JOIN Table3 ON...
INNER JOIN Table0 t1Codes ON t3Codes.Code = Table3.Codes
INNER JOIN Table4 ON...
INNER JOIN Table0 t1Codes ON t4Codes.Code = Table4.Codes

If you're just unioning them, or using in separate queries, then you don't have to worry about query engine ambiguity with Table0 -- but it may still be a good idea to alias it for your benefit.

gwb SSC Veteran Points: 218 More actions · Answer 4

Thank you all for the assistance. I think ultimately using the UNION ALL method was the trick, will report back once final results are in.