4

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.

Mahmoud Gamal
  • 78,257
  • 17
  • 139
  • 164
user1170332
  • 41
  • 1
  • 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: http://stackoverflow.com/questions/2384298/mysql-full-outer-join-syntax-error – ben Jan 26 '12 at 00:30

1 Answers1

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.

Dan Soap
  • 10,114
  • 1
  • 40
  • 49
  • 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 .... – user1170332 Jan 26 '12 at 00:55
  • fixed the error, thanks for pointing it out (I removed the `dates` alias from the subselect table) – Dan Soap Jan 26 '12 at 13:08
  • 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. – user1170332 Jan 26 '12 at 17:21