Home

Fix for Working NOT EXISTS sql query

April 16, 2009 - 9:05am — burningdog
  1. #this query now works. It shows me all vehicles which are available on a particular day
  2.  
  3. SELECT node.nid AS nid,
  4. node.title AS title
  5. FROM node
  6. WHERE (node.STATUS <> 0)
  7. AND (node.type IN ('vehicle'))
  8. AND NOT EXISTS (
  9.   SELECT nid FROM content_type_vehicle_booking WHERE node.nid = (
  10.     SELECT content_field_car.field_car_nid AS nid
  11.     FROM node
  12.     LEFT JOIN content_field_car ON node.nid = content_field_car.nid
  13.     LEFT JOIN content_field_date ON content_field_date.nid = content_field_car.nid
  14.     WHERE (node.STATUS <> 0)
  15.     AND (node.type IN ('vehicle_booking'))
  16.     AND (DATE_FORMAT(content_field_date.field_date_value, '%Y-%m-%d') <= '2009-04-11' AND
  17.          DATE_FORMAT(content_field_date.field_date_value2, '%Y-%m-%d') >= '2009-04-11')
  18.   )
  19. )

Submit Fix

Any tags you'd like to associate with your code, delimitered by commas (example: Views, CCK, Module, etc).
Select the syntax highlighting mode to use.