E J E J - 10 months ago 60
MySQL Question

MySQL INNER JOIN returning all rows

I have two tables 'hotels' and 'flights' that have a 'typeId' field that reference 'itineraries' table foreign key 'id'.
For example: hotel.foreign('tripId').references('id').inTable('itineraries');

I want to select all rows from each table that have an itinerary id of 3. I tried the following but I am receiving all ids in itinerary, not just 3.

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

Answer Source

You should manage the inner join for flights too and filter by where for 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