INNER JOIN в SQL, где одна таблица содержит несколько результатов

У меня есть две таблицы:

Первый стол:

MEMBERS:
ID    |    FIRSTNAME    |    LASTNAME    |
1          Jack              Smith
2          Jane              Baker
3          Peter             Little

И вторая таблица:

DETAILS:    
SUBSCRIBER_ID    |    FIELD_ID    |    FIELD_VALUE    |
1                     1                Blue
1                     2                Dogs
1                     3                March
2                     1                Pink
2                     2                Cats
2                     3                June
3                     1                Black
3                     2                Birds
3                     3                September

FIELD_ID представляет другие сведения о члене. т.е.

1 = Favorite color
2 = Favorite animal
3 = Favorite month

Я хочу получить отчет, который содержит их основную информацию MEMBERS и их дополнительную информацию из таблицы DETAILS, где SUBSCRIBER_ID = ID в Членах. Так что для Джейн это будет выглядеть так:

  • Джейн
  • пекарь
  • розовый
  • коты
  • июнь

ОБНОВЛЕНО:

Я использовал следующий оператор SQL и извлекаю нужные данные, но отображение не такое, как я ищу:

ВЫБЕРИ участников., подробности. от участников ВНУТРЕННЕЕ СОЕДИНЕНИЕ подробнее ON members.id = details.subscriber_id ГДЕ jos_osmembership_subscribeers.id = '29'

(ГДЕ чисто свести запись записи к одному члену).

Результат, который я получаю здесь:

Jane | Baker | Pink
Jane | Baker | Cats
Jane | Baker | June

В то время как я ищу

Jane | Baker | Pink | Cats | June

Я использую следующий вывод, который я знаю, на 100% неправильно:)

if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        echo "Results:" . $row["ID"] . "|";
        echo "" . $row["FIRSTNAME"] . "|";
        echo "" . $row["LASTNAME"] . "|";
        echo "" . $row["field_value"] . "|";
}

На самом деле я ищу что-то, что позволит мне выводить как:

if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        echo "Results:" . $row["ID"] . "|";
        echo "" . $row["FIRSTNAME"] . "|";
        echo "" . $row["LASTNAME"] . "|";
        echo "" . $row["color"] . "|";
        echo "" . $row["animal"] . "|";
        echo "" . $row["month"] . "|";
}

ура

Источник

3 ответа

Используйте и INNER JOIN

SELECT ID, FIRSTNAME, LASTNAME, SUBSCRIBER_ID, FIELD_ID, FIELD_VALUE
FROM MEMBERS m
INNER JOIN DETAILS d ON m.ID = d.SUBSCRIBER_ID
Источник

Какую базу данных вы используете? Если это будет оракул, попробуйте sys connect by path, чтобы все три детали подряд на человека приходились примерно так:

SELECT id, 
       firstname, 
       lastname, 
       Sys_connect_by_path(field_value, ',') 
FROM   members m, 
       details d 
WHERE  m.id = d.subscriber_id 
START WITH id = 1 
CONNECT BY PRIOR subscriber_id = id 
ORDER  BY id
Источник

В основном у вас есть как минимум 3 варианта:

  1. использовать inner join как предложил Мэтт, и позаботьтесь о форматировании данных на уровне представления (в вашем случае, PHP).
    Основным преимуществом этого подхода является то, что его легко писать и поддерживать, и он не требует каких-либо изменений в вашей базе данных.
  2. Создайте представление, которое будет вращать вашу таблицу данных, и используйте inner join на этом столе.
    Основное преимущество заключается в том, что вы будете выбирать только те данные, которые вам нужны, и сможете сохранить дизайн вашей базы данных.
  3. Измените структуру базы данных и переместите детали в столбцы в таблице участников.
    Основным недостатком этого подхода является то, что он требует изменения в вашей базе данных, но главное преимущество заключается в том, что все станет проще.
    Не используйте эту опцию, если данные, которые вы сохраняете, подвержены изменениям (я имею в виду, если вы хотите продолжать добавлять / удалять детали для каждого участника). если это так, то используйте первый вариант, так как он будет самым простым для написания и обслуживания.

читать о динамическом пивоте в MySQL

прочитайте о том, когда лучше использовать EAV (модель Entity–attribute–value)

Источник
Другие вопросы по тегам