1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Pull all parent records with or without child record - if child record exists select latest...

Discussion in 'Programming/Internet' started by MGrove, Oct 8, 2018.

  1. MGrove

    MGrove Guest

    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
    )

    Login To add answer/comment
     

Share This Page