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



  • 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?



  • 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.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2