anuj bajpai anuj bajpai - 5 months ago 9
MySQL Question

sql query for minus operation

SELECT flight_id, depart, arr, price
FROM user.flightdb t2 LEFT JOIN
user.booking t1
ON t1.flightID = t2.flight_id
WHERE t2.source = 1 and t2.dest = 4 and
t1.date = "02/02/2016" and t1.flightID IS NULL;


I want to select those flight_id from flightdb(table) who should have source and destination as given and out of them which do not appear in the booking(table) at a particular date.

so what will be the query in mysql

Answer

You can use a left join, but the conditions on the second table need to be in the on clause. Also, you should use a proper date format:

SELECT flight_id, depart, arr, price 
FROM user.flightdb t2 LEFT JOIN
     user.booking t1
     ON t1.flightID = t2.flight_id and t1.date = '2016-02-02'
WHERE t2.source = 1 and t2.dest = 4 and t1.flightID IS NULL;