SQL: How to join specific values from a large number columns and a large number of tables into one query?

Question

I am a bit of a newbie at SQL (and I apologize if this has been answered elsewhere although I can't seem to find anything that nails it) and have been stumped for ages on this problem. As a foreword, I have researched and know about various joins and inner joins etcetera and I understand how they work and they seem to work well for smaller amounts of data. However, my problem is that the number of columns and number of tables is so large, I need to know if there is a simpler way to join this data into one query.

So this is what I have:

 $query2 = "SELECT fen_toit,tuiles,zinc,bois,bac_acier FROM phase1 WHERE id IN(3,12,22)";
    $query3 = "SELECT fen_toit,tuiles,zinc,bois,bac_acier FROM phase2 WHERE id IN(3,12,22)";
    $query4 = "SELECT fen_toit,tuiles,zinc,bois,bac_acier FROM phase3 WHERE id IN(3,12,22)";
    $query5 = "SELECT fen_toit,tuiles,zinc,bois,bac_acier FROM phase4 WHERE id IN(3,12,22)";
    $query6 = "SELECT fen_toit,tuiles,zinc,bois,bac_acier FROM phase5 WHERE id IN(3,12,22)";
    $query7 = "SELECT fen_toit,tuiles,zinc,bois,bac_acier FROM phase6 WHERE id IN(3,12,22)";
    $query8 = "SELECT fen_toit,tuiles,zinc,bois,bac_acier FROM phase7 WHERE id IN(3,12,22)";
    $query9 = "SELECT fen_toit,tuiles,zinc,bois,bac_acier FROM phase8 WHERE id IN(3,12,22)";
    $query10 = "SELECT fen_toit,tuiles,zinc,bois,bac_acier FROM phase9 WHERE id IN(3,12,22)";
    $query11 = "SELECT fen_toit,tuiles,zinc,bois,bac_acier FROM phase10 WHERE id IN(3,12,22)";
    $query12 = "SELECT fen_toit,tuiles,zinc,bois,bac_acier FROM phase11 WHERE id IN(3,12,22)";
    $query13 = "SELECT fen_toit,tuiles,zinc,bois,bac_acier FROM phase12 WHERE id IN(3,12,22)";

I would like to combine these query results into one query. I have tried a simple comma separation between phase1, phase2 e.t,c, but I am thrown an ambiguous column error. I understand this is due to the fact that I did not add

phase1.fen_toit

OR

phase1.tuiles

and so on to the SELECT, as this seems ridiculously long!

If anyone could help I would be extremely grateful. I am sure the answer is staring me in the face!

You can also use the MERGE storage engine to create a table that automatically performs a UNION between all these tables. — Barmar
– Barmar, Commented Feb 6, 2015 at 0:22

Rigel1121 · Accepted Answer · 2015-02-06 01:21:47Z

Join your queries using UNION or UNION ALL which looks like below:

$query2 = "SELECT fen_toit,tuiles,zinc,bois,bac_acier FROM phase1 WHERE id IN(3,12,22)
             UNION ALL
           SELECT fen_toit,tuiles,zinc,bois,bac_acier FROM phase2 WHERE id IN(3,12,22)
             UNION ALL
           SELECT fen_toit,tuiles,zinc,bois,bac_acier FROM phase3 WHERE id IN(3,12,22)
             UNION ALL
           SELECT fen_toit,tuiles,zinc,bois,bac_acier FROM phase4 WHERE id IN(3,12,22)
             UNION ALL
           SELECT fen_toit,tuiles,zinc,bois,bac_acier FROM phase5 WHERE id IN(3,12,22)
             UNION ALL
           SELECT fen_toit,tuiles,zinc,bois,bac_acier FROM phase6 WHERE id IN(3,12,22)
             UNION ALL
           SELECT fen_toit,tuiles,zinc,bois,bac_acier FROM phase7 WHERE id IN(3,12,22)
             UNION ALL
           SELECT fen_toit,tuiles,zinc,bois,bac_acier FROM phase8 WHERE id IN(3,12,22)
             UNION ALL
           SELECT fen_toit,tuiles,zinc,bois,bac_acier FROM phase9 WHERE id IN(3,12,22)
             UNION ALL
           SELECT fen_toit,tuiles,zinc,bois,bac_acier FROM phase10 WHERE id IN(3,12,22)
             UNION ALL
           SELECT fen_toit,tuiles,zinc,bois,bac_acier FROM phase11 WHERE id IN(3,12,22)
             UNION ALL
           SELECT fen_toit,tuiles,zinc,bois,bac_acier FROM phase12 WHERE id IN(3,12,22)";

Collectives™ on Stack Overflow

SQL: How to join specific values from a large number columns and a large number of tables into one query?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related