MySQL多表查询：按指定日期筛选供应商可用车辆及剩余座位

Hey there! Let's sort out this query so it correctly shows all vehicles from your selected supplier with their remaining seats during the specified time window.

First, let's recap your table structures for clarity: #

vehicle table:

vid  vehicleName  noOfSeat  sid
========================================
13   x            10        28
14   x            8         28
15   x            12        28
16   x            14        29
17   x            4         29
18   x            8         30

assigned table:

asgid  bid  sid  vid  did  seatBooked  assigned_start       assigned_end
========================================================================
56     15   28   13   17   3           06/01/2018 02:01     06/01/2018 04:02
57     15   28   14   15   2           06/01/2018 02:01     06/01/2018 04:02
58     15   28   15   16   3           06/01/2018 02:01     06/01/2018 04:02

The Problem with Your Current Query #

Your existing query uses NOT IN to exclude any vehicle that has any booking in the time window. But this is wrong because a vehicle might have some seats booked but still have remaining seats available—those should still show up with their remaining count, not be excluded entirely.

The Correct Query Approach #

We need to:

1. Get all vehicles for the selected supplier (sid = $value)
2. Left join with the assigned table to find all bookings that overlap with your time window
3. Calculate the total number of seats booked for each vehicle in that window
4. Subtract the booked seats from the vehicle's total seats to get remaining seats (if no bookings, remaining seats = total seats)

Here's the corrected SQL query you can use inside your loop:

SELECT 
    v.vid,
    v.noOfSeat AS total_seats,
    COALESCE(SUM(a.seatBooked), 0) AS seats_booked,
    (v.noOfSeat - COALESCE(SUM(a.seatBooked), 0)) AS seats_available
FROM 
    vehicle v
LEFT JOIN 
    assigned a ON v.vid = a.vid 
               AND a.sid = v.sid
               AND NOT (a.assigned_end < '$timestart' OR a.assigned_start > '$timeend')
WHERE 
    v.sid = $value
GROUP BY 
    v.vid, v.noOfSeat
ORDER BY 
    v.vid;

Let's Break This Down: #

• LEFT JOIN: Ensures we keep all vehicles from the supplier, even if they have no bookings in the time window.
• Time Overlap Check: The condition NOT (a.assigned_end < '$timestart' OR a.assigned_start > '$timeend') correctly identifies bookings that overlap with your selected window. This is better than BETWEEN because it covers all overlapping scenarios (e.g., a booking starts before your window but ends during it, or starts during and ends after).
• COALESCE: Handles vehicles with no bookings—SUM(a.seatBooked) would be NULL, so COALESCE turns that into 0, making the subtraction work correctly.
• GROUP BY: Aggregates the booked seats per vehicle, so we get a single row per vehicle with total, booked, and available seats.

What This Returns #

For your example time window (06/01/2018 02:01 to 06/01/2018 04:02) and supplier sid=28, the result will match your desired output:

And for suppliers 29 and 30, it will show their vehicles with 14, 4, and 8 available seats respectively.

A Quick Security Note #

Your current code uses string interpolation for $timestart, $timeend, and $value—this is a SQL injection risk. Make sure to switch to prepared statements with parameter binding instead of directly inserting variables into your query!

内容的提问来源于stack exchange，提问作者user8625234