MySQL: Getting ROWS of table B as COLUMNS in the result of the join with table A. A smarter / better way than this?

Question

I did my homework and looked for information on the net and in StackOverflow but I did not find an answer to my question. I looked at:

Creating two colums from rows of a table (did not understand it) MySQL join same table display rows in columns way (complicated) (different and problem does not seem the same) Creating two colums from rows of a table

I used this to build my query:

How to Display rows as Columns in MySQL?

I have a table "lessons" that goes like this:

ID  title
---------
1   Math
2   Latin
3   English
4   Science 

I have a second table "results" that stores the type of test user have passed for each lesson. There are currently two types of tests: o and w (there could be more in the future). The table goes like this:

lesson_id  usr_id  test_type   course_id
----------------------------------------
1          100       o          1
1          100       w          1
1          24        o          1
1          36        w          1

In the table above user 100 passed test o and w for the Math. In the table above user 24 passed test o for the Math. In the table above user 36 passed test w for the Math.

Now I would like to get a report for user 100, I would like to have something like:

ID  title      o       w
------------------------------
1    Math       TRUE    TRUE
2    Latin      FALSE   FALSE
3    English    FALSE   FALSE
4    Science    FALSE   FALSE

For user 36:

ID  title      o       w
------------------------------
1    Math       FALSE    TRUE
2    Latin      FALSE   FALSE
...

For user 24:

ID  title      o       w
------------------------------
1    Math       TRUE    FALSE
2    Latin      FALSE   FALSE
...

Any other user:

ID  title      o       w
------------------------------
1    Math       FALSE   FALSE
2    Latin      FALSE   FALSE
...

I can accomplish this with a query as follows:

SELECT l.id, l.title, COALESCE(s.o,FALSE) AS o, COALESCE(t.w, FALSE) AS w FROM lessons l 
LEFT JOIN (
    SELECT r.lesson_id,
    CASE
        WHEN r.test_type='o' THEN TRUE
        ELSE FALSE
    END AS o
    FROM results r WHERE r.itype='o' AND r.usr_id=100
    ) AS s ON l.id=s.lesson_id 
LEFT JOIN (
    SELECT rr.lesson_id,
    CASE
        WHEN rr.test_type='w' THEN TRUE
        ELSE FALSE
    END AS w 
    FROM results rr WHERE rr.test_type='w' AND rr.usr_id=100
    ) AS t ON l.id=t.lesson_id
WHERE l.course_id=1 ORDER BY l.id ASC;

While this code seems to work (still busy testing it), I do not like it because it requires two joins and each join is made of selects from a table that could turn out to be quite large in the future. This query would be run often

1. Can you suggest a better way to do this (better can mean smarter, more straightforward, faster or other design... or any other suggestion :-) )?

2. In case I need to stick with this, could you recommend references as how to set the best indexes to run this query fast? Links, articles you used?

3. What happens where more test_types will be created? My query uses only w and o what if tomorrow there are more? Is there a generic way to do this kind of thing?

Any help / advice is more than welcome,

Philippe

Answer 1

What you are trying to do is called a PIVOT in other databases. Unfortunately, MySQL does not have one so you will have to create one using an aggregate function and a CASE statement.

If you know you will only have two test_type values, then you can hard-code this:

select l.id,
  l.title,
  max(case when test_type = 'o' then 'true' else 'false' end) as o,
  max(case when test_type = 'w' then 'true' else 'false' end) as w
from lessons l
left join results r
  on l.id = r.lesson_id
  and r.usr_id = 100  -- the user id will change for each person
group by l.id, l.title

See SQL Fiddle with Demo

Now if you want to perform this dynamically, meaning you do not know ahead of time the number of test_types to transpose, then you should review the following article:

Dynamic pivot tables (transform rows to columns)

Your code would look like this:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(CASE WHEN test_type = ''',
      test_type,
      ''' THEN ''true'' ELSE ''false'' END) AS ',
      test_type
    )
  ) INTO @sql
FROM Results;


SET @sql 
  = CONCAT('SELECT l.id,
      l.title, ', @sql, ' 
     from lessons l
     left join results r
       on l.id = r.lesson_id
       and r.usr_id = 100
     group by l.id, l.title');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

See SQL Fiddle with Demo

Answer 2

I see this as a simple aggregation query. The aggregation is by lesson_id and title. The calculation then just pivots the results for o and w:

select l.lesson_id, l.title,
       max(case when r.test_type = 'o' then 'TRUE' else 'FALSE' end) as o,
       max(case when r.test_type = 'w' then 'TRUE' else 'FALSE' end) as w
from lessons l left outer join
     results r
     on l.lesson_id = r.lesson_id
where r.user_id = <whatever>
group by l.lesson_id, l.title
order by 1

Choosing by the user at this level is a problem because it needs to come from results, which may be NULL due to the left outer join. Here is a variation that fixes the problem:

select l.lesson_id, l.title,
       max(case when r.test_type = 'o' then 'TRUE' else 'FALSE' end) as o,
       max(case when r.test_type = 'w' then 'TRUE' else 'FALSE' end) as w
from lessons l left outer join
     results r
     on l.lesson_id = r.lesson_id and
        r.user_id = <whatever>
group by l.lesson_id, l.title
order by 1

By moving the condition to the "on" clause, we are guaranteed that all lessons will be kept.