MySQL INNER JOIN 返回所有行

Question

我有两个表“酒店”和“航类”，它们都有一个“typeId”字段，该字段引用“行程”表外键“id”。例如:hotel.foreign('tripId').references('id').inTable('itineraries');

我想从每个表中选择行程 id 为 3 的所有行。我尝试了以下操作，但我收到了行程中的所有 id，而不仅仅是 3。

SELECT itineraries.* , hotels.* , flights.* 
FROM itineraries 
INNER JOIN hotels ON hotels.tripId = itineraries.id 
INNER JOIN flights ON flights.tripIid = 3

Answer 1

您还应该管理航类的内部联接，并按 tripIid =3 的位置进行过滤

SELECT itineraries.* , hotels.* , flights.* 
FROM itineraries 
INNER JOIN hotels ON hotels.tripId = itineraries.id 
INNER JOIN flights ON flights.tripIid = itineraries.id 
WHERE  flights.tripIid = 3

给你最后一条评论

How can i edit this so that it still returns all rows if there is a hotel with matching tripID but no flights?

你应该使用左连接

SELECT itineraries.* , hotels.* , flights.* 
FROM itineraries 
INNER JOIN hotels ON hotels.tripId = itineraries.id 
LEFT JOIN flights ON flights.tripIid = itineraries.id 
WHERE  hotels.tripId = 3