Присоедините одну строку к нескольким строкам в другой таблице

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

Люди

Name  Age
--------
Jane  27
Joe   36
Jim   16

Свойства

Name   Property
-----------------
Jane   Smart
Jane   Funny
Jane   Good-looking
Joe    Smart
Joe    Workaholic
Jim    Funny
Jim    Young

Я хотел бы написать эффективный выбор, который будет выбирать людей по возрасту и возвращать все или некоторые из их свойств.

Ex: People older than 26
Name Properties
Jane Smart, Funny, Good-looking
Joe Smart, Workaholic

Также приемлемо возвращать одно из свойств и общее количество объектов.

Запрос должен быть эффективным: в таблице людей есть миллионы строк, сотни тысяч строк в таблице свойств (поэтому у большинства людей нет свойств). Одновременно выбираются сотни строк.

Есть ли способ сделать это?

Ответ 1

Использование:

   SELECT x.name,
          GROUP_CONCAT(y.property SEPARATOR ', ')
     FROM PEOPLE x
LEFT JOIN PROPERTIES y ON y.name = x.name
    WHERE x.age > 26
 GROUP BY x.name

Вы хотите, чтобы функция MySQL GROUP_CONCAT (документация), чтобы вернуть список значений PROPERTIES.property, разделенный запятыми.

Я использовал LEFT JOIN, а не JOIN, чтобы включить записи PEOPLE, которые не имеют значения в таблице PROPERTIES. Если вам нужен только список людей со значениями в таблице PROPERTIES, используйте:

   SELECT x.name,
          GROUP_CONCAT(y.property SEPARATOR ', ')
     FROM PEOPLE x
     JOIN PROPERTIES y ON y.name = x.name
    WHERE x.age > 26
 GROUP BY x.name

Я понимаю, что это пример, но использование имени - это плохой выбор для референциальной целостности, когда вы считаете, сколько "Джона Смита" существует. Назначение user_id, являющееся числовым значением, было бы лучшим выбором.

Ответ 2

Вы можете использовать INNER JOIN, чтобы связать две таблицы вместе. Дополнительная информация о JOINs.

SELECT *
FROM People P
INNER JOIN Properties Pr
  ON Pr.Name = P.Name
WHERE P.Name = 'Joe' -- or a specific age, etc

Однако часто гораздо быстрее добавить уникальный первичный ключ в таблицы, подобные этим, и создать index для увеличения скорости.

Скажите, что таблица People имеет поле id
И таблица Properties имеет поле peopleId, чтобы связать их вместе

Затем запрос будет выглядеть примерно так:

SELECT *
FROM People P
INNER JOIN Properties Pr
  ON Pr.id = P.peopleId
WHERE P.Name = 'Joe'

Ответ 3

SELECT x.name,(select GROUP_CONCAT(y.Properties SEPARATOR ', ')
FROM PROPERTIES y 
WHERE y.name.=x.name ) as Properties FROM mst_People x

попробуйте это