CODEWITHSUNDEEP
mysqlsql
Chris Mccabe
Chris Mccabe

Reputation: 1951

sql join on two fields in one table

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;

heres my structure

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 ;

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

update for anyone interested in this and joining a 3rd table

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;

Upvotes: 2

Views: 660

Answers (2)

sll
sll

Reputation: 62484

 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

Upvotes: 1

Joko Nardi
Joko Nardi

Reputation: 21

    select people.first_name as firstname,
            people.last_name as lastname,
            people.organisation,
            booking.dated,
            booking.car,
            booking.slot from booking
left join people on booking.person_1 = people.id

OR

select people.first_name as firstname,
            people.last_name as lastname,
            people.organisation,
            booking.dated,
            booking.car,
            booking.slot
 from booking
left join people on booking.person_1 = people.id or booking.person_2 = people.id

check that...if you still need help will help you

Upvotes: 0

Related Questions