E J E J - 1 month ago 7
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

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