MySQL Joining 3 tables with 2 common attributes

Question

I have 3 tables with the following structure:

Table_A:

Date, ID, A1, A2.

Table_B:

Date, ID, B1.

Table_C:

Date, ID, C1, C2, C3.

I want to join all three into a table with attributes: Date, ID, A1, A2, B1, C1, C2, C3.

For the 3 tables some of the Dates and IDs are the same but some are not so I want to do something like a LEFT JOIN with a UNION and then RIGHT JOIN so that I don't lose any of the Date and ID rows. It seems like I need the Date and ID combinations to get their own unique identifiers so that I can then join the tables but I have hit a road block.

Sound like you want a FULL OUTER JOIN, unfortunately mysql does not support it. Your idea of using UNION is probably the best way to go about it. Check this our for examples: stackoverflow.com/questions/2384298/… — ben
– ben, Commented Jan 26, 2012 at 0:30

Dan Soap · Accepted Answer · 2012-01-26 13:07:36Z

3

You might try this one:

SELECT Date, ID, A1, A2, B1, C1, C2, C3
FROM (
    SELECT Date, ID
    FROM Table_A
    UNION
    SELECT Date, ID
    FROM Table_B
    UNION
    SELECT Date, ID
    FROM Table_B
) d
LEFT JOIN Table_A a on (d.Date=a.Date and d.ID=a.ID)
LEFT JOIN Table_B b on (d.Date=b.Date and d.ID=b.ID)
LEFT JOIN Table_C c on (d.Date=c.Date and d.ID=c.ID)

This should give you the answer you are looking for, however I assume that the statement will be quite slow for large sets of data.

edited Jan 26, 2012 at 13:07

answered Jan 26, 2012 at 0:08

Dan Soap

10.3k2 gold badges43 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user1170332 Over a year ago

This is the error that I keep getting when trying your above suggestion: Error Code: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'd LEFT JOIN ....

Dan Soap Over a year ago

fixed the error, thanks for pointing it out (I removed the dates alias from the subselect table)

user1170332 Over a year ago

Unfortunately my data set is very large , over 300,000 rows for just Table_A. I ran the query for an hour and a half before I stopped it. Do you have any suggestions for a query that might be faster? Thank you so much.

Collectives™ on Stack Overflow

MySQL Joining 3 tables with 2 common attributes

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related