0

My database contains a table tab0 with two columns, id and mjd, and thousands of tables tab1... tabM with five columns, id,A,B,C, and D. The columns contain thousands of elements.

Which is the best way to obtain something like this?

+-----+-------------+-------------+-------------+
| mjd | A (of tab_1)| A (of tab_2)| A (of tab_m)|
+-----+-------------+-------------+-------------+
|  1  | 123         | 423         | 523         |
|  2  | 233         | 243         | 633         |
| ... | ...         | ...         | ...         |
|  n  | 353         | 343         | 753         |
+-----+-------------+-------------+-------------+

Can I obtain the list of columns and tables from INFORMATION_SCHEMA and then use it to construct my query like

SELECT t0.mjd, t1.A, t2.A, ... tM.A FROM tab0 as t0, tab1 as t1, ... tabM as tM 
WHERE  t0.id=t1.id and ... and t0.id=tM.id;

or it is a completely insane approach?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
leonard vertighel
  • 1,058
  • 1
  • 18
  • 37
  • I suppose you could also create a dynamic statement based on the table list from `INFORMATION_SCHEMA` – Kermit Apr 19 '13 at 01:14
  • 1
    @ChinBoon Not a duplicate. That question is about one table with lots of columns. This is about lots of tables. – Barmar Apr 19 '13 at 01:27
  • 2
    Ever think of a redesign with fewer tables? – Richard Apr 19 '13 at 01:33
  • @Richard , I am quite new in databases. Every table contain the light arriving from 4 telescopes (ABCD) every millisecond from a star in a field. The fields are hundreds, the stars in each field are thousands and the milliseconds are many :) The `tab0` table contains the timing (the same for all the stars in the field). Have you any suggestion to reduce the number of tables, which are basically one for each star? – leonard vertighel Apr 19 '13 at 01:40
  • 2
    There is a proposal that this question is a duplicate of [MySQL Select many fields — How best to display in terminal](http://stackoverflow.com/questions/924729/mysql-select-many-fields-how-best-to-display-in-terminal). This is not a duplicate of that question, as noted by Barmar. – Jonathan Leffler Apr 19 '13 at 16:41
  • Trying to construct _by hand_ the query, I had the `Too many tables; MySQL can only use 61 tables in a join` error. As the tables are thousands, I think that I will have to redesign all. Some of you have any idea? – leonard vertighel Apr 19 '13 at 18:57

0 Answers0