mysql collect 1:n relation into one record

Question

I have a 1:n relation:

Rule  1:n  Examples

For simplification you can assume that Rule and the Examples only consists one string attribute. I'm looking for a sql statement where I get a table with the following columns:

rule; example_1; example_2; example_3

I don't care about the examples 4 and higher...

Answer 1

I feel like there's a better way to do this that I'm overlooking, but this seems to work fine. I'm self-joining the ordered examples table, where each successive example value must greater than the previous one (thus yielding each of the top three).

SELECT
  r.rule,
  e1.example AS example_1,
  e2.example AS example_2,
  e3.example AS example_3
FROM rules r
LEFT JOIN (SELECT * FROM examples e ORDER BY example) e1
  ON e1.ruleID = r.ruleID
LEFT JOIN (SELECT * FROM examples e ORDER BY example) e2
  ON e2.ruleID = r.ruleID
  AND e2.example > e1.example
LEFT JOIN (SELECT * FROM examples e ORDER BY example) e3
  ON e3.ruleID = r.ruleID
  AND e3.example > e2.example
GROUP BY r.ruleID

Result:

|  RULE | EXAMPLE_1 | EXAMPLE_2 | EXAMPLE_3 |
|-------|-----------|-----------|-----------|
| rule1 |       ex1 |       ex2 |       ex3 |
| rule2 |       ex5 |       ex6 |       ex7 |
| rule3 |       ex8 |    (null) |    (null) |
| rule4 |    (null) |    (null) |    (null) |

Sample data:

/* rules table */
| RULEID |  RULE |
|--------|-------|
|      1 | rule1 |
|      2 | rule2 |
|      3 | rule3 |
|      4 | rule4 |

/* examples table */
| EXAMPLE | RULEID |
|---------|--------|
|     ex1 |      1 |
|     ex2 |      1 |
|     ex3 |      1 |
|     ex4 |      1 |
|     ex5 |      2 |
|     ex6 |      2 |
|     ex7 |      2 |
|     ex8 |      3 |

Here's a working SQL Fiddle example.