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.

This would be easier if you could pivot the table (ie: turn columns into rows). AFAIK MySQL doesn't have this capability.
–
NullUserExceptionAug 29 '11 at 3:57

The problem with that is that there are hundreds of rows after id #1 in the A tables.
–
abarringtonAug 29 '11 at 3: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.
–
NullUserExceptionAug 29 '11 at 4: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.
–
abarringtonAug 29 '11 at 4:08

1 Answer
1

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)