我有一张叫“预订”的桌子
> id status
1 P
2 P
还有一个叫做“通话”
id calldate type booking
1 01/01/2012 DEL 1
2 01/02/2012 COL 1
3 01/03/2012 DEL 2
4 31/12/2019 COL 999
我想在“预订”一次列出每个记录,将来自“通话”的关联记录显示为这样的另一列:
bookingId deliverydate collectiondate
1 01/01/2012 01/02/2012
2 01/03/2012 null
我试过了:
select `b`.`bookingid` AS `bookingid`,
`del`.`calldate` AS `Delivery`,
`col`.`calldate` AS `Collection`
from `booking` `b`
left join `call` `del` on `b`.`bookingid` = `del`.`booking`
left join `call` `col` on `b`.`bookingid` = `col`.`booking`
where ((`del`.`type` = 'DEL') OR (`col`.`type` = 'COL') and (`b`.`status` = 'P'));
但我得到Bookingid 1列出了3次。有人可以修复我的联接吗?
最佳答案
我认为您想将类型移动到联接条件中:
select `b`.`bookingid` AS `bookingid`,
`del`.`calldate` AS `Delivery`,
`col`.`calldate` AS `Collection`
from `booking` `b`
left join `call` `del` on `b`.`bookingid` = `del`.`booking` AND `del`.`type` = 'DEL'
left join `call` `col` on `b`.`bookingid` = `col`.`booking` AND `col`.`type` = 'COL'
where `b`.`status` = 'P';
关于mysql - MySQL-两次连接同一表,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/13943350/