1

I'm building a table of ALL records on vacation watch which have not expired and are not already scheduled for a visit

  • Select the property information
  • Select the mapzone cars
  • Select the last time the property was checked

My problem is if the property has never been checked the record is not selected

The code is:

FROM vacation_watch_master
LEFT JOIN property 
    ON vacation_watch_master.MstrLink = property.MstrLink
LEFT JOIN property_map_carzone 
    ON property.MapZone = property_map_carzone.MAP_ZONE
LEFT JOIN vac_watch_check_dates v1 
    ON vacation_watch_master.VWLink = v1.VWLink
WHERE v1.VWDateLink = (SELECT max(VWDateLink) 
                       FROM vac_watch_check_dates
                       WHERE vac_watch_check_dates.VWLink = vacation_watch_master.VWLink
                      )
HAVING vacation_watch_master.Watch_Expired  > -1 
   AND vacation_watch_master.MstrLink NOT IN (
                              SELECT vac_watch_schedule.MstrLink 
                              FROM vac_watch_schedule
                                             )
  • 1
    You need to have a SELECT (something) before your FROM. Your code would be much clearer if you use the ability to identify a block of code (use {} button when editing). – joanolo Dec 25 '16 at 0:36
  • 1
    There may be an interesting question here, can you share table definitions, sample data and expected output? Please read through help us help you and edit your questioh – Tom V Dec 25 '16 at 0:40
0

In your WHERE clause, your v1.VMDateLink = SELECT MAX(VWDateLink) ... will not be true since the MAX will return NULL, and NULL is never equal to anything including NULL.

Try adding OR v1.VMDateLink IS NULL, but in general, I would create an inline view on vac_watch_check_dates with a single row per VWLink with the max VMDateLink.

  • If i add the IS NULL statement the query becomes a run-away query (Never ends) – MGrove Dec 26 '16 at 22:12
  • The program returns all the data I need EXCEPT for the properties that have never had a date checked record – MGrove Dec 26 '16 at 22:13
  • Are you suggesting I create a separate query of the Vacation Check table which will just contain a single record for each VWLink then access that query instead of the table? – MGrove Dec 26 '16 at 22:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.