SELECT something JOIN ON something IN columns of multiple tables

I'm trying to integrate a bunch of SELECTs all into one.

I have multiple tables that look similar to the following:

Table: A1

+---+----+----+----+----+
|id | I1 | I2 | I3 | I4 |
+---+----+----+----+----+
| 1 |  5 |  6 |  1 |  3 |
+---+----+----+----+----+

Table: A2

+---+----+----+----+
|id | I1 | I2 | I3 |
+---+----+----+----+
| 1 |  3 | 10 |  5 |
+---+----+----+----+

And 1 table that looks like this:

Table: Standards

+---+---------+
|id |   Name  | 
+---+---------+
| 1 | 'one'   |
| 2 | 'two'   | 
| 3 | 'three' |
| 4 | 'four'  | 
| 5 | 'five'  | 
| 6 | 'six'   |
| 7 | 'seven' |
| 8 | 'eight' |
| 9 | 'nine'  | 
|10 | 'ten'   |
+---+---------+

PHP Code

<?php
    while(/*"A" tables*/){
        $noi = // number of columns in this A table
        $id = // id of this A table
        $columns = "";
    开发者_如何学编程    for ($i=1; $i<=$noi; $i++){
            $columns = $columns . "A" . $id . ".I" . $i . ", ";
        }
        $columns = rtrim($columns, ", ");

        $sql = "SELECT s.* FROM A" . $id . "
            INNER JOIN Standards AS s
            ON s.id IN (" . $columns . ") 
            WHERE A" . $id . ".id=1
            ORDER BY s.id ASC";

        $result = mysql_query($sql);
    }
?>

I'd like to combine all of these SELECTS into 1. I was thinking I could run a for loop to generate all the names of the tables, but I wasn't sure exactly how to join them with the other JOIN ON ... IN (...) that's in there.

The results I want to generate are:

+---+---------+
|id |   Name  | 
+---+---------+
| 1 | 'one'   |
| 3 | 'three' | 
| 5 | 'five'  | 
| 6 | 'six'   |
|10 | 'ten'   |
+---+---------+

---

Take a look at this solution, it should give you a foundation of how to do this:

select-mysql-rows-but-rows-into-columns-and-column-into-rows

In short, you might want to try to select the values from each row's columns as rows from the 2 tables and UNION those datasets, then JOIN them onto the lookup table. The result should be what you described. (I am assuming that all of the I1, I2, I3, etc. columns represent IDs in your lookup table)