Sandeep Pujare Sandeep Pujare - 7 months ago 11
SQL Question

Mysql join query failure

master_vehicle_inventory table
mvi_id
1
2
3
4

member_booking table

mb_id mb_startdate mb_returndate mvi_id
100 22-04-2016 30-04-2016 2
101 23-01-2016 02-05-2016 3


These are my tables, I need output of the rows that are not there in the member_booking table from master_vehicle_inventory table as well as i need those results from master_vehicle_inventory which don't fall between the range of mb_startdate and mb_returndate

can anyone help me on this, so far i have tried this

SELECT mb.mb_id, mb.mb_startdate, mb.mb_returndate, mvi.mvi_id FROM master_vehicle_inventory AS mvi LEFT JOIN LEFT JOIN member_booking AS mb on mvi.mvi_id=mb.mvi_id WHERE CURDATE()NOT BETWEEN mb.mb_startdate AND mb.mb_returndate AND mvi.mvi_id NOT IN(SELECT mvi.mvi_id FROM member_booking)


But it doesn't give the results i want

Answer

To return rows from `master_vehicle_inventory` which do not have a "matching" row in `member_booking` table, we can use an anti-join pattern.

Assuming \mb_startdate` and `mb_enddate` are defined as datatype DATE.

Something like this:

SELECT mvi.mvi_id
  FROM master_vehicle_inventory mvi
  LEFT
  JOIN member_booking mb
    ON mb.mvi_id       =  mvi.mvi_id
   AND mb.mb_startdate <= DATE(NOW())
   AND mb.mb_enddate   >  DATE(NOW())
 WHERE mb.mvi_id IS NULL

(The specification for a "matching" row is a bit vague.)

Given DATE(NOW()) returns '2016-04-25' we expect that query to return rows

mvi_id
------
1
4

(We are suspicious that the datatype of the columns may not be DATE because the values shown in the example data are not in the YYYY-MM-DD format we expect returned for a DATE expression.)

Comments