Join two tables without mixing rows

I want to join 2 tables and for each joined result I need a separate row, but the rows of my query result are somehow mixed:


Schema + Data:

Join two tables without mixing rows


My Query:

SELECT Table_A.id,

       Table_B.id,

       Table_C.id

FROM   Table_A

LEFT JOIN Table_B
       ON Table_B.id_a = Table_A.id

LEFT JOIN Table_C
       ON Table_C.id_a = Table_A.id

The Result:

Join two tables without mixing rows

What I want (3 separate rows – separated by the join-tables’ id-column):

Join two tables without mixing rows

Q: What am I doing wrong?

=> You could put it in that way: I need all rows of where id_a is 1 + table_a.id=1. Maybe JOIN is the wrong approach here..?

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

You need INNER JOIN UNION :

SELECT A.id A_Id, B.id B_Id, ''c_id
FROM Table_A A
INNER JOIN Table_B B ON B.id_a = A.id

UNION ALL

SELECT A.id A_Id, '' B_Id, c.id c_id
FROM Table_A A
INNER JOIN Table_C C ON A.id = C.id_a


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x