SQLize Online / PHPize Online  /  SQLtest Online

/*select s.seat_no, b.seat_no, ticket_no --count(s.seat_no) total_seats, --count(b.ticket_no) occupied_seats from boarding_passes b right join flights f on b.flight_id = f.flight_id right join seats s on f.aircraft_code = s.aircraft_code and (b.seat_no = s.seat_no or s.seat_no is null) where b.flight_id = 30625 --or (b.flight_id is null and b.ticket_no is null) --group by f.aircraft_code order by LPAD(SUBSTRING(s.seat_no FROM '^[0-9]+'), 2, '0'), SUBSTRING(s.seat_no FROM '[A-Z]+$'); */ /* select * from seats s left join flights f on f.aircraft_code = s.aircraft_code left join boarding_passes b on b.flight_id = f.flight_id and s.seat_no = b.seat_no where b.flight_id = 30625 or b.ticket_no is null --select * from seats where aircraft_code = '773' */ SELECT COUNT(DISTINCT s.seat_no) AS total_seats, COUNT(bp.seat_no) AS occupied_seats FROM flights f JOIN seats s ON f.aircraft_code = s.aircraft_code LEFT JOIN boarding_passes bp ON f.flight_id = bp.flight_id AND s.seat_no = bp.seat_no WHERE f.flight_id = 30625; ; select * from query_cost('/*select s.seat_no, b.seat_no, ticket_no --count(s.seat_no) total_seats, --count(b.ticket_no) occupied_seats from boarding_passes b right join flights f on b.flight_id = f.flight_id right join seats s on f.aircraft_code = s.aircraft_code and (b.seat_no = s.seat_no or s.seat_no is null) where b.flight_id = 30625 --or (b.flight_id is null and b.ticket_no is null) --group by f.aircraft_code order by LPAD(SUBSTRING(s.seat_no FROM ''^[0-9]+''), 2, ''0''), SUBSTRING(s.seat_no FROM ''[A-Z]+$''); */ /* select * from seats s left join flights f on f.aircraft_code = s.aircraft_code left join boarding_passes b on b.flight_id = f.flight_id and s.seat_no = b.seat_no where b.flight_id = 30625 or b.ticket_no is null --select * from seats where aircraft_code = ''773'' */ SELECT COUNT(DISTINCT s.seat_no) AS total_seats, COUNT(bp.seat_no) AS occupied_seats FROM flights f JOIN seats s ON f.aircraft_code = s.aircraft_code LEFT JOIN boarding_passes bp ON f.flight_id = bp.flight_id AND s.seat_no = bp.seat_no WHERE f.flight_id = 30625;');