CODEWITHSUNDEEP
mysql
Juice
Juice

Reputation: 3063

How to write query for this condition

I am doing a project for a travel agency. i have to joing 3 tables

While booking i need to search the vehicles available in that date range in rac_details . 

      $qry="    SELECT `vehicle_details`.*, `owner`.`owner_name`, `owner`.`id` As owner_id, 
    `rac_details`.`from_date`, `rac_details`.`to_date`, 
    `rac_details`.`vehicle_id`
     FROM (`vehicle_details`)
     LEFT JOIN `rac_details` ON `rac_details`.`vehicle_id`=`vehicle_details`.`id`
     AND `rac_details`.`from_date` NOT BETWEEN $from_timestamp AND $to_timestamp 
     AND `rac_details`.`to_date` NOT BETWEEN $from_timestamp AND $to_timestamp 
     JOIN `owner` ON `owner`.`id`=`vehicle_details`.`owner_id`
 WHERE `vehicle_details`.`model_id` =  '$model_id' ";

By the above query i am getting all vehicle details. when inner join rac_details i am not able to get the newly added vehicle ie which do not have record in rac_details. Hope anyone can help me

Edit

For example i got a booking for Polo with reg no XX-YY-ZZ from feb 27th to march 3. then i need to search whether the vehicle xx-yy-zz is available in that days for that i just searched in my rac_details where i store booked vehicle details. Then we bought a new vehicle Vento with reg no RR-TT-YY so i added that vehicle in my vehicle_details table and no records will be added to rac_details before the first booking

if i write this query 

 $qry="   SELECT `vehicle_details`.*, `owner`.`owner_name`, `owner`.`id` As owner_id, 
    `rac_details`.`from_date`, `rac_details`.`to_date`, 
    `rac_details`.`vehicle_id`
     FROM (`vehicle_details`)
     JOIN `rac_details` ON `rac_details`.`vehicle_id`=`vehicle_details`.`id`
     AND `rac_details`.`from_date` NOT BETWEEN $from_timestamp AND $to_timestamp 
     AND `rac_details`.`to_date` NOT BETWEEN $from_timestamp AND $to_timestamp 
     JOIN `owner` ON `owner`.`id`=`vehicle_details`.`owner_id`
 WHERE `vehicle_details`.`model_id` =  '$model_id' ";

when i book vento for the first time it will not show vento in the list since there is no record in rac_details for vento

Upvotes: 1

Views: 69

Answers (1)

Gavriel
Gavriel

Reputation: 19237

I would try it with NOT IN the already reserved vehicles:

"SELECT `vehicle_details`.*, `owner`.`owner_name`, `owner`.`id` As owner_id, 
`rac_details`.`from_date`, `rac_details`.`to_date`, 
`rac_details`.`vehicle_id`
 FROM (`vehicle_details`)
 JOIN `owner` ON `owner`.`id`=`vehicle_details`.`owner_id`
 WHERE `vehicle_details`.`model_id` =  '$model_id' 
 AND vehicle_details.id NOT IN (
   SELECT rac_details.vehicle_id
   FROM rac_details
   WHERE `rac_details`.`from_date` BETWEEN $from_timestamp AND $to_timestamp 
     AND `rac_details`.`to_date` BETWEEN $from_timestamp AND $to_timestamp
 )"

Upvotes: 1

Related Questions