Sandeep Pujare Sandeep Pujare - 2 years ago 57
SQL Question

Mysql join query failure

master_vehicle_inventory table

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 Source

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
  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


(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.)

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download