How to limit results from a SQL subquery or join

Lets imagine I have 2 tables in MySQL, one called Vehicle and the other called Passenger.

If I want a complete list of all Vehicles and their passengers then I can do something like this:

SELECT * 
  FROM Vehicle v 
  LEFT 
  JOIN Passenger p
    ON p.VehicleID = v.VehicleID 
 LIMIT 0,100

The problem here is lets imagine that my vehicles are buses, and the first has 50 passengers, the 2nd bus has 40 and the 3rd has 30. The Limit 100 on the above query would give me a partial list of passengers on the 3rd bus.

Is there a way create such a query that won't split the results from the joined table?

Or alternatively can you apply LIMITS separately to the different tables? So I could say I want a limit of 10 vehicles and a limit of 50 passengers per vehicle?

Logically something like this:

SELECT * FROM Vehicle (LEFT JOIN Passenger ON Passenger.VehicleID = Vehicle.VehicleID LIMIT 0,50) LIMIT 0, 10

I was wondering if this could be achieved using some kind of subquery? Maybe something like:

SELECT *, (SELECT * FROM Passenger WHERE Passenger.VehicleID = Vehicle.VehicleID LIMIT 0,50) FROM Vehicle LIMIT 0, 10

But this doesn't work (The subquery is only allowed to return a single row).

Thanks in advance.

Solution

In MySQL, the easiest way to do what you want is using variables to enumerate the rows:

SELECT * 
FROM (SELECT v.*, (@rnv := @rnv + 1) as seqnum_v
      FROM Vehicle v CROSS JOIN
           (SELECT @rnv := 0) params
     ) v LEFT JOIN
     (SELECT p.*,
             (@rnp := if(@v = VehicleId, @rnp + 1,
                         if(@v := VehicleId, 1, 1)
                        )
             ) as seqnum_p
      FROM Passenger p CROSS JOIN
           (SELECT @v := -1, @rnp := 0) params
     ) p
     ON p.VehicleID = v.VehicleID 
WHERE seqnum_v <= 10 and seqnum_p <= 50;