SQL - Find passenger who has boarded with exactly one specific company

[Amritpal]

Cheers, I have the following entities inside my database:

1. Passenger(id,name,surname, dof)
2. Company (id, name, code)
3. Trains (id, model)
4. Company has Trains (company_id, trains_id)
5. Trip (id, date , route_id, train_id)
6. Passenger_takes_trip (trip_id, passenger_id)

I have to return the name and surname of the passengers who have only taken trips with one specific company, e.g. named : 'Example_Company'. I find this to be a bit tricky, since there are many arrays that I will have to take into account, but I don't know how to combine them exactly.

I know I should start from passenger, then go on to find the trips with their corresponding trains and after that combine with the company, and I know that the basic idea is to find that there doesn't exists a second trip that has been taken with another company.

Could anyone provide me with some help or a solution, preferably without using Join?

[zymir]

Here's a solution that does not use joins:

SELECT p.name, p.surname
FROM Passenger AS p
WHERE EXISTS (
  SELECT passenger_id
  FROM Passenger_Takes_Trip
  WHERE passenger_id = p.id
  AND train_id IN (
    SELECT trains_id
    FROM Company_Has_Trains
    WHERE company_id = (
      SELECT id FROM Company WHERE name = 'Example_Company'
    )
  )
)
AND NOT EXISTS (
  SELECT passenger_id
  FROM Passenger_Takes_Trip
  WHERE passenger_id = p.id
  AND train_id IN (
    SELECT trains_id
    FROM Company_Has_Trains
    WHERE company_id <> (
      SELECT id FROM Company WHERE name = 'Example_Company'
    )
  )
)

The solution that I would use, which uses joins:

SELECT p.name, p.surname
FROM Passenger AS p
JOIN Passenger_Takes_Trip AS pt ON p.id = pt.passenger_id
JOIN Company_Has_Trains AS ct ON pt.train_id = ct.trains_id
JOIN Company AS c ON ct.company_id = c.id
GROUP BY p.id
HAVING GROUP_CONCAT(DISTINCT c.name) = 'Example_Company'

I am accustomed to using joins. But I know it takes some practice. You have to get used to thinking of data as sets that can be combined using these kinds of relational operations.