Home

Fix for Showing vehicle nodes which don't exist in vehicle_booking nodes

April 16, 2009 - 6:58am — burningdog
  1. #this SQL query has 2 parts. Part 1 shows all vehicles and vehicle bookings nodes (both are CCK types). This returns 4 rows (3 vehicles + 1 vehicle booking)
  2.  
  3. SELECT node.nid AS nid,
  4. content_field_car.field_car_nid AS field_car_nid,
  5. content_field_date.field_date_value,
  6. content_field_date.field_date_value2
  7. FROM node
  8. LEFT JOIN content_field_car ON node.nid = content_field_car.nid
  9. LEFT JOIN content_field_date ON node.nid = content_field_date.nid
  10. WHERE (node.STATUS <> 0) AND (node.type IN ('vehicle_booking','vehicle'))
  11.  
  12. #part 2 shows all vehicle bookings which have been made. Returns a single row (a vehicle booking).
  13.  
  14. SELECT content_field_car.field_car_nid AS nid
  15. FROM node
  16. LEFT JOIN content_field_car ON node.nid = content_field_car.nid
  17. WHERE (node.STATUS <> 0) AND (node.type IN ('vehicle_booking'))
  18.  
  19. #*****************************************************
  20. # what I need is to subtract the results of the 2nd query from the results in the 1st query.
  21. # i.e. to show all vehicles which do NOT have a vehicle booking
  22. # to do this, I use NOT EXISTS, like so:
  23.  
  24. SELECT node.nid AS nid,
  25. content_field_car.field_car_nid AS field_car_nid,
  26. content_field_date.field_date_value,
  27. content_field_date.field_date_value2
  28. FROM node
  29. LEFT JOIN content_field_car ON node.nid = content_field_car.nid
  30. LEFT JOIN content_field_date ON node.nid = content_field_date.nid
  31. WHERE (node.STATUS <> 0) AND (node.type IN ('vehicle_booking','vehicle'))
  32.  
  33. AND NOT EXISTS (
  34. SELECT content_field_car.field_car_nid AS nid
  35. FROM node
  36. LEFT JOIN content_field_car ON node.nid = content_field_car.nid
  37. WHERE (node.STATUS <> 0) AND (node.type IN ('vehicle_booking'))
  38. )
  39.  
  40. # this returns 0 rows; it should return 1 row.

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.