Join Third Table Based on Join Between Two Other Tables

[UltraSmooth]

I need to make a join between 3 tables, where the 3rd table is dependent on the join of the first two. Here's an example to elaborate,

Table A
A.col1
A.col2
...
A.colN

Table B
B.col1
B.col2
B.col3
...
B.colN

Table C
C.col1
C.col2
C.col3
...
C.colN

I need col1 from table A, col2 from table B and col3 from table C.

Table A and B are joined on col1. Table C is related to table A on col1 and table C is related to table B on col2. What's the most efficient way to write this query? So far I have something that looks like this (but is not working quite right):

SELECT A.col1,B.col2,C.col3
FROM A
JOIN B
ON A.col1=B.col1
JOIN C
ON C.col1=A.col1
AND C.col2=B.col2

When I add in the join with table C my result set returns more records than it should. Of course my real world example has a lot more columns but basically this is what I'm doing. Table C requires the join on tables A and B to get the columns it needs to join.

Am I on the right track? Is it not the proper way to make this join?

Thanks for any help!

 

[r937]

yes, that is the proper way to join them

i can't help you figure out why you're getting too many rows, because i have no idea what the col1/col2 columns actually mean, and you did not reveal what the one-to-many relationships between your tables are



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[UltraSmooth]

Thanks for you feedback. At least I know I'm on the right track with the join structure.

I believe the reason I'm getting more records than I should is due to what you mentioned, the one-to-many relationships. Table A has a one-to-many relationship with tables B and C. I'll do some more testing and see what I can figure out.

 

[r937]

Table A has a one-to-many relationship with tables B and C.

more testing isn't going to fix this


you can't return two one-to-many sets in the same query without cross join effects

try two separate queries

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon