2

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'   |
+---+---------+
abarrington
  • 166
  • 9
  • This would be easier if you could pivot the table (ie: turn columns into rows). AFAIK MySQL doesn't have this capability. – NullUserException Aug 29 '11 at 03:57
  • The problem with that is that there are hundreds of rows after id #1 in the A tables. – abarrington Aug 29 '11 at 03:59
  • I am curious though; why are the tables structured like this? When this kind of problem appears, it could be a sign of bad db design. – NullUserException Aug 29 '11 at 04:03
  • I'll be the first to admit that it's bad DB design. The tables each represent a multiple choice test that each have different numbers of test items (the columns). The rows are each new administration of that test. – abarrington Aug 29 '11 at 04:08

1 Answers1

0

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)

Community
  • 1
  • 1
Dmitry Samuylov
  • 1,554
  • 2
  • 14
  • 37