CODEWITHSUNDEEP
sqljoin
AutomationTherapist33
AutomationTherapist33

Reputation: 3

SQL trying to do a JOIN to include results from multiple Tables

I'm a complete novice teaching myself SQL by writing and modifying a few queries and reports at work. I've got something of a handle on the various types of JOINs and I've used INNER JOIN a few times with decent success.

What I'm stuck on should be a simple task, but my Google-Fu must be weak. Here's what I'm trying to do.

Say I have 3 tables, Table_A, Table_B, and Table_C, and each table has a column called [Serial_Number].

What I'm wanting to select is 3 of the other columns if A.Serial_Number = B.Serial_Number OR C.Serial_Number.

I've tried doing:

SELECT 
   * 
FROM 
  Table_A AS A 
  INNER JOIN Table_B AS B ON A.Serial_Number = B.Serial_Number
  INNER JOIN Table_C AS C ON A.Serial_Number = C.Serial_Number

But this always yields 0 results as the nature of the data dictates that if A matches B, it will never match C and vice versa. I also tried a LEFT OUTER JOIN as the second clause, but this just includes NULLs from Table_C that have already matched on Table_B.

All the searches I have done relating to JOINs on multiple tables seem to be about using JOINS to further exclude records, where I'm actually wanting to INCLUDE more records.

Like I said, I'm sure this is really simple, just needing a nudge in right direction.

Thanks!

Upvotes: 0

Views: 159

Answers (2)

T I
T I

Reputation: 9943

The use of two inner joins here is akin to saying

If A.Serial_Number = B.Serial_Number AND    
   A.Serial_Number = C.Serial_Number

Using left outer join on the second clause - by which i presume you mean second join - would perform a left join on a result set already filtered by A.Serial_Number = B.Serial_Number by the first inner join. Given that B.Serial_Number doesn't relate to C.Serial_Number you wouldn't expect the an equijoin to return any result from tablec.

What you want is a left outer join like you tried but for both tableb and tablec.

Select *
From tablea
Left join tableb on tableb.Serial_Number = tablea.Serial_Number
Left join tablec on tablec.Serial_Number = tablea.Serial_Number

This way regardless of whether tablea.Serial_Number is in tableb it will still be returned and thus available to be joined to tablec

Upvotes: 0

Michael Gresham
Michael Gresham

Reputation: 53

Agreed. Your output for your inner joins is producing NULLs which is why it is resulting in 0. I would suggest modifying your INNER JOIN.

Upvotes: 0

Related Questions