How can I use rows in a lookup table as columns in a MySQL query?

Question

I'm trying to build a MySQL query that uses the rows in a lookup table as the columns in my result set.

LookupTable

id  |  AnalysisString
1   |  color
2   |  size
3   |  weight
4   |  speed

ScoreTable

id  |  lookupID | score | customerID
  1 |      1    |   A   |     1
  2 |      2    |   C   |     1 
  3 |      4    |   B   |     1 
  4 |      2    |   A   |     2
  5 |      3    |   A   |     2
  6 |      1    |   A   |     3 
  7 |      2    |   F   |     3

I'd like a query that would use the relevant lookupTable rows as columns in a query so that I can get a result like this:

customerID |  color  | size  | weight  | speed
     1          A        C                 D
     2                   A        A    
     3          A        F

The kicker of the problem is that there may be additional rows added to the LookupTable and the query should be dynamic and not have the Lookup IDs hardcoded. That is, this will work:

SELECT st.customerID, 
(SELECT st1.score FROM ScoreTable st1 WHERE lookupID=1 AND st.customerID = st1.customerID) AS color,
(SELECT st1.score FROM ScoreTable st1 WHERE lookupID=2 AND st.customerID = st1.customerID) AS size,
(SELECT st1.score FROM ScoreTable st1 WHERE lookupID=3 AND st.customerID = st1.customerID) AS weight,
(SELECT st1.score FROM ScoreTable st1 WHERE lookupID=4 AND st.customerID = st1.customerID) AS speed
FROM ScoreTable st
GROUP BY st.customerID

Until there is a fifth row added to the LookupTable . . .

Perhaps I'm breaking the whole relational model and will have to resolve this in the backend PHP code?

Thanks for pointers/guidance.

tom

Answer 1

You have architected an EAV database. Prepare for a lot of pain when it comes to maintainability, efficiency and correctness. "This is one of the design anomalies in data modeling." (http://decipherinfosys.wordpress.com/2007/01/29/name-value-pair-design/)

The best solution would be to redesign the database into something more normal.

Answer 2

What you are trying to do is generally referred to as a cross-tabulation, or cross-tab, query. Some DBMSs support cross-tabs directly, but MySQL isn't one of them, AFAIK (there's a blog entry here depicting the arduous process of simulating the effect).

Two options come to mind for dealing with this:

1. Don't cross-tab at all. Instead, sort the output by row id, then AnalysisString, and generate the tabular output in your programming language.
2. Generate code on-the-fly in your programming langauge to emit the appropriate query.
3. Follow the blog I mention above to implement a server-side solution.

Also consider @Marek's answer, which suggests that you might be better off restructuring your schema. The advice is not a given, however. Sometimes, a key-value model is appropriate for the problem at hand.