user3780616 user3780616 - 23 days ago 6
MySQL Question

MySQL Easily Join Several Tables

All,
I have several tables, over 50, who have a common column "MYID" and can't figure out an easier way to join them without writing a gigantic query.

Imagine the following sample (only with 50 tables and 50 data points)

SELECT DATAPOINT1, DATAPOINT2, DATAPOINT3, DATAPOINT4, DATAPONT5
FROM TABLE1, TABLE2, TABLE3, TABLE4, TABLE5
WHERE TABLE1.MYID = TABLE2.MYID, MYTABLE2.MYID=MYTABLE3.MYID, MYTABLE4.MYID=MYTABLE5.MYID


How would I achieve the above results with a shorter query?

Answer

Looks like a horrible database design. The query you are showing is about what you need. Many, many tables to join, but that's the way it is with the design given. It cannot be shorter, because you need to list all the columns, all the tables and all criteria. There is nothing you can remove.

With proper join syntax:

select 
  t1.datapoint1, 
  t2.datapoint2, 
  t3.datapoint3,
  ...
from table1 t1
join table2 t2 on t2.myid = t1.myid
join table3 t3 on t3.myid = t1.myid
join ...

Very easy to generate with a programming language loop or Excel for instance.

Comments