Многократное соединение SQL для многих ко многим таблицам + разделение запятыми

Я согласен с ответом Алекса Мартелли, что вы должны получить данные в нескольких строках и выполнить некоторую обработку в своем приложении.

Если вы попытаетесь сделать это с помощью простых объединений, вам нужно будет присоединиться к таблице людей для каждого типа ролей, и если в каждой роли есть несколько человек, ваш запрос будет иметь декартовы произведения между этими ролями.

Поэтому вам нужно сделать это с помощью GROUP_CONCAT() и создать скалярный подзапрос в списке выбора для каждого роль:

SELECT m.id, m.uri, 
 (SELECT GROUP_CONCAT(name) 
  FROM media_to_people JOIN people ON (people_id = id) 
  WHERE media_id = m.id AND role = 1) AS Actors,
 (SELECT GROUP_CONCAT(name) 
  FROM media_to_people JOIN people ON (people_id = id) 
  WHERE media_id = m.id AND role = 2) AS Artists,
 (SELECT GROUP_CONCAT(name) 
  FROM media_to_people JOIN people ON (people_id = id) 
  WHERE media_id = m.id AND role = 3) AS Publishers
FROM media m;

Это действительно уродливо! Не пытайтесь повторить это дома!

Воспользуйтесь нашим советом и не пытайтесь форматировать сводную таблицу, используя только SQL.

SQLite не имеет функции "поворота", которая может понадобиться для начинающих, а часть "значения, разделенные запятыми" определенно является проблемой презентации, которую было бы абсурдно (и, возможно, невозможно) пытаться протолкнуть на любой уровень базы данных, независимо от того, может быть задействован диалект SQL - это определенно часть работы, которую вы выполняете на клиенте, например средство отчетности или язык программирования.

Используйте SQL для доступа к данным и оставьте представление другим слоям.

Как вы получаете свои данные

SELECT media.id, media.uri, people.name, people.role
FROM media
JOIN media_to_people ON (media.id = media_to_people.media_id)
JOIN people ON (media_to_people.people_id = people.id)
WHERE media.id = ?
ORDER BY people.role, people.name

(? - это один из способов указать параметр в SQLite, который будет привязан к конкретному идентификатору мультимедиа, который вы ищете, способами, которые зависят от вашего клиента); данные будут поступать из БД в ваш клиентский код в несколько строк, и ваш клиентский код может легко поместить их в желаемую форму с одним столбцом.

Нам сложно сказать, как кодировать клиентскую часть, ничего не зная о среде или языке, которые вы используете в качестве клиента. Но, например, в Python:

def showit(dataset):
  by_role = collections.defaultdict(list)
  for mediaid, mediauri, name, role in dataset:
    by_role[role].append(name)
  headers = ['mediaid', 'mediauri']
  result = [mediaid, mediauri]
  for role in sorted(by_role):
    headers.append('people(%s)' % role)
    result.append(','.join(by_role[role]))
  return ' '.join(headers) + '\n' + ' '.join(result)

даже это не совсем соответствует вашей спецификации - вы запрашиваете заголовки, такие как «люди (исполнитель)», в то же время указываете, что роль закодирована как int, и не упоминаете способ перехода от int к строке «исполнитель» , так что, очевидно, невозможно точно соответствовать вашей спецификации ... но это настолько близко, насколько может быть моя изобретательность ;-).