sql在一个表中的两个字段上联接 [英] sql join on two fields in one table

问题描述

我有一个包含两个人的预订表-我想将person_1作为一行返回，person_2作为新行返回，但其id与people表相关.

i have bookings table which has two people- i want to return person_1 as a row, person_2 as a new row but with the person's id related to the people table

这是我所了解的-但没有提供预订信息

This is as far as i got-but doesnt pull in booking info

 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.我在努力拥有两个字段并将它们关联到一个列表中

any ideas on how i could get a result set like- person.first_name, person.last_name, person.organisation, booking.dated, person.car, person.slot. im struggling with having two fields and having them to relate them into the one list

更新有兴趣并加入第三张桌子的任何人

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

heres my final query with php vars to pull in my certain dates and slots and also join a third table

    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;

推荐答案

 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

这篇关于sql在一个表中的两个字段上联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！