CODEWITHSUNDEEP
mysql
Muhammad Umar
Muhammad Umar

Reputation: 11782

Getting all rows from one table provided the certain condition meets from another table in mysql

I have mysql query as 

var query = "SELECT * FROM drivers WHERE status = 1 AND lat BETWEEN " + min_lat + " AND " + max_lat + " AND lng BETWEEN " + min_lon + " AND " + max_lon + " AND isAvailable = 1 LIMIT 1";

I want to add one more condition. Suppose i have a table named as 

booking 
driverId   bookingStatus

Now i want to get all drivers with above query + condition that booking table row count should be 0 where bookingStatus is less than 3, driverId exists in booking table

Upvotes: 0

Views: 24

Answers (1)

Shadow
Shadow

Reputation: 34232

Left join the drivers table on the bookings and filter the bookings on the bookingstatus field in the join condition. If there is no such row in the bookings table that match the condition, then driverid in bookings table will be null in the resultset.

SELECT d.*
FROM drivers d
LEFT JOIN booking b ON d.driverid=b.driverid and b.bookingStatus<3
WHERE d.status = 1 AND d.lat BETWEEN ... AND ...
      AND d.lng BETWEEN ... AND ...
      AND d.isAvailable = 1
      AND b.driverid is null
LIMIT 1

If you specifically need to check if the driver exists in the bookings table, then add an exists subquery to the where condition.

Upvotes: 1

Related Questions