-1

I have a database table which includes physical education lessons. A lesson can have multiple lesson types like "warming-up", "game" and "technical instruction". Those types are stored in another table. I have a third table which matches the types to lessons.

| lessonId | title       |
|----------|-------------|
| 1        | Test Lesson |

| typeId | label                 |
|--------|-----------------------|
| 1      | warming-up            |
| 2      | game                  |
| 3      | technical instruction |

| lessonId | typeId |
|----------|--------|
| 1        | 1      |
| 1        | 2      |
| 1        | 3      |

I use PDO in PHP to retrieve the lessons from the database. Now I'd like to get the lesson and it's types with one query. The result (encoded as JSON) should look like this. It would be fine if the query gives the types back as a comma-separated list, which I can then parse with PHP.

{
  "lessonId" : 1,
  "title" : "Test Lesson",
  "types" : [
    {
      "typeId" : 1,
      "label" : "warming-up"
    },
    {
      "typeId" : 2,
      "label" : "game"
    },
    {
      "typeId" : 3,
      "label" : "technical instruction"
    }
  ]
}

What SQL query should I use to get this result?

JimProgrammer
  • 7
  • 2
  • I found a solution here: [link](https://stackoverflow.com/q/10956188/9127701) . My question has been closed so I can't add it as an answer or mark my question as a duplicate. – JimProgrammer Nov 15 '20 at 11:23

1 Answers1

1

You can use JSON functions:

select l.*,
    (
        select json_arrayagg(json_object('typeid', t.typeid, 'label', t.label))
        from lesson_types lt
        inner join types t on t.typeid = lt.typeid
        where lt.lessonid = l.lessonid
    ) as types
from lessons l
GMB
  • 216,147
  • 25
  • 84
  • 135