mysql - sql连接一张表中的两个字段

Question

我有一个有两个人的 bookings 表 - 我想将 person_1 作为一行返回，person_2 作为新行返回，但此人的 ID 与 people 表相关

这是我得到的，但没有提取预订信息

 SELECT people.* FROM (
    (select booking.person_1 as id from booking)
    union ALL
    (select booking.person_2 as id from booking)
) as peopleids
join people on people.id = peopleids.id;

这是我的结构

CREATE TABLE IF NOT EXISTS `booking` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `slot` enum('morning_drive','afternoon_loop','return_drive') NOT NULL,
  `type` enum('911','vintage_911') NOT NULL,
  `car` int(11) NOT NULL,
  `person_1` int(11) DEFAULT NULL,
  `person_2` int(11) DEFAULT NULL,
  `dated` date NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;


CREATE TABLE IF NOT EXISTS `people` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(50) NOT NULL,
  `last_name` varchar(50) NOT NULL,
  `organisation` varchar(100) NOT NULL,
  `event_date` date NOT NULL,
  `wave` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;

关于如何获得结果集的任何想法，如 person.first_name、person.last_name、person.organisation、booking.dated、person.car、person.slot。我正在为拥有两个字段并将它们关联到一个列表中而苦苦挣扎

任何对此感兴趣并加入第 3 table 的人的更新

这是我使用 php vars 的最终查询，以提取我的特定日期和时段并加入第三个表

    SELECT peopleids.id, 
       peopleids.car, 
       cars.nr, 
       p.first_name, 
       p.last_name, 
       p.organisation, 
       p.event_date, 
       p.wave 
FROM (SELECT booking.car, booking.person_1 as id FROM booking WHERE booking.dated = '".$date."' AND booking.`slot` =  '".$slot."' 
        union ALL SELECT booking.car, booking.person_2 as id FROM booking  WHERE booking.dated = '".$date."' AND booking.`slot` = '".$slot."' 
     ) as peopleids
LEFT JOIN people p ON p.id = peopleids.id  LEFT JOIN cars on cars.id = peopleids.car;

Answer 1

 SELECT 
       ag.id, 
       p.first_name, 
       p.last_name, 
       p.organisation, 
       p.event_date, 
       p.wave
 FROM (
      SELECT booking.person_1 as id, booking.Car as car FROM booking
      union ALL
      SELECT booking.person_2 as id, booking.Car as car FROM booking
     ) as ag
JOIN people p ON people.id = ag.id;
INNER | LEFT JOIN Cars c ON c.ID = ag.car