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, 3 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: 100948 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) A socialist is someone who will give you the shirt off *someone else's* back.

Phil Parkin SSC Guru Points: 246695 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

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

ratbak Ten Centuries Points: 1333 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.