MYSQL 多表多数据

Question

我有一个数据库，用于存储事件和相关数据。我想逐行打印事件数据。我的问题是 2 或 3 个成员(cere_leader、member1 和可选的 member2)可能附加到每个事件，我无法创建以有用的形式查询从何处获取成员数据。

数据库的相关表行:

CREATE TABLE cities (
  c_id int(11) NOT NULL,
  c_name varchar(255) NOT NULL
)

CREATE TABLE `events` (
  e_id int(11) NOT NULL,
  e_event_type_id int(11) NOT NULL,
  e_member1_id int(11) NOT NULL,
  e_member2_id int(11) DEFAULT NULL,
  e_city_id int(11) NOT NULL,
  e_date date NOT NULL,
  e_cere_leader_id int(11) NOT NULL
)

CREATE TABLE event_types (
  et_id int(11) NOT NULL,
  et_name varchar(255) NOT NULL
)

CREATE TABLE members (
  m_id int(11) NOT NULL,
  m_title varchar(10) DEFAULT NULL,
  m_surname varchar(255) NOT NULL,
  m_forename varchar(255) NOT NULL
)


ALTER TABLE cities
  ADD PRIMARY KEY (c_id);

ALTER TABLE `events`
  ADD PRIMARY KEY (e_id);

ALTER TABLE event_types
  ADD PRIMARY KEY (et_id);

ALTER TABLE members
  ADD PRIMARY KEY (m_id);


ALTER TABLE cities
  MODIFY c_id int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `events`
  MODIFY e_id int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE event_types
  MODIFY et_id int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE members
  MODIFY m_id int(11) NOT NULL AUTO_INCREMENT;
COMMIT;

结果的每一行应该是这样的

Array
(
    [type] => Esküvő
    [date] => 2018-08-01
    [city] => Iszapszentgilisztás
    [member1] => Bubi Béla
    [member2] => Cupi Cecília
    [cleader] => Dr Duci Dávid
)

我试过这个:

SELECT
 e.e_id,
 e.e_date,
 e.e_cere_leader_id,
 e.e_member1_id,
 e.e_member2_id,
 GROUP_CONCAT(m.m_title),
 GROUP_CONCAT(m.m_surname),
 GROUP_CONCAT(m.m_forename),
 et.et_name,
 c.c_name
FROM
 `events` AS e,
 members AS m,
 event_types AS et,
 cities AS c
WHERE
 (
 e.e_cere_leader_id = m.m_id OR(
 e.e_member1_id = m.m_id OR e.e_member2_id = m.m_id
 )
 ) AND e.e_event_type_id = et.et_id AND e.e_city_id = c.c_id
GROUP BY
 e.e_id

但似乎我以随机顺序获取成员数据，我无法处理它(Kozma Amira 在所有情况下都是 cere_leader)

Array
(
 [e_id] => 7
 [e_date] => 2017-10-31
 [e_cere_leader_id] => 12258
 [e_member1_id] => 15045
 [e_member2_id] => 0
 [GROUP_CONCAT(m.m_title)] => ,vitéz
 [GROUP_CONCAT(m.m_surname)] => Kozma,Horthi
 [GROUP_CONCAT(m.m_forename)] => Amira,Myklós
 [et_name] => Keresztelő
 [c_name] => Hort
)
Array
(
 [e_id] => 8
 [e_date] => 2017-12-31
 [e_cere_leader_id] => 12258
 [e_member1_id] => 15046
 [e_member2_id] => 0
 [GROUP_CONCAT(m.m_title)] => gróf,
 [GROUP_CONCAT(m.m_surname)] => Szécshenjy,Kozma
 [GROUP_CONCAT(m.m_forename)] => Ezsvány,Amira
 [et_name] => Temetés
 [c_name] => Fedémes
)
Array
(
 [e_id] => 9
 [e_date] => 2017-12-31
 [e_cere_leader_id] => 12258
 [e_member1_id] => 15046
 [e_member2_id] => 15047
 [GROUP_CONCAT(m.m_title)] => az,,gróf
 [GROUP_CONCAT(m.m_surname)] => Iványi,Kozma,Szécshenjy
 [GROUP_CONCAT(m.m_forename)] => Álmos,Amira,Ezsvány
 [et_name] => Esküvő
 [c_name] => Budapest 2. kerület
)

有人可以帮我制作正确的 SQL 表达式吗？

Answer 1

您可以加入members 表3 次。使用 LEFT JOIN 是因为 member2 是可选的。

select et.et_name as `type`, c.c_name as city, e.e_date as date,
   concat(m1.m_forename, ' ', m1.m_surname) as member1,
   concat(m2.m_forename, ' ', m2.m_surname) as member2,
   concat(m3.m_forename, ' ', m3.m_surname) as leader
from events e
   join event_types et on et.et_id = e.e_event_type_id 
   join cities c on c.c_id = e.e_city_id
   join members m1 on e.e_member1_id = m1.m_id
   left join members m2 on e.e_member2_id = m2.m_id
   join members m3 on e.e_cere_leader_id = m3.m_id