No result set when Where clause with ID not used

Question

I am trying to figure out why my code does not return any data when I take out the Account_ID. I want it to run and find every Account_ID that meets the join conditions and the fields I want returned. This is in MySQL and I have looked around here and Googled but have not found anything like this or just not put it correctly. So, below is code and if anyone could help me I would greatly appreciate it. The code should return 1 row per Account_ID I am not used to using MySQL. Version 5.6.34 using Toad Data-Point to run sql script. Thanks Dan

    SELECT DISTINCT
    pd.ACCOUNT_ID                 
    , do.MEMBER_NAME
    , do.GENDER
    , MaxDate.EarlyDATE AS ACTIVITY_DATE
    , MaxDate.LateDATE  AS LAST_ACTIVITY_DATE
    , pd.NUMBER_WKS        
    ... more fields after this
    Joins below
    FROM ZZ_Program_Details pd 
        LEFT JOIN ZZ_Demographics do 
         ON do.ACCOUNT_ID = pd.ACCOUNT_ID
    INNER JOIN 
        (SELECT DISTINCT cl.ACCOUNT_ID
        , min(cl.activity_date)  AS EarlyDATE
        , cl1.activity_date AS LateDATE
        , cl1.scheduling_selection
        , cl.ATND_STATUS_THIS_INTERVAL         
        , cl.WEIGHT_STATUS_FOR_INTERVAL        
        , TRUNCATE(cl.WEIGHT_WHEN_ENROLLED,2)   AS WEIGHT_WHEN_ENROLLED
        , TRUNCATE(cl.WEIGHT_AT_INTERVAL_END,2) AS WEIGHT_AT_INTERVAL_END
        FROM ZZ_Class_Attendance cl
         INNER JOIN ZZ_Class_Attendance cl1
          ON cl1.account_id = cl.ACCOUNT_ID 
        WHERE cl1.ACTIVITY_DATE > cl.ACTIVITY_DATE
         /*and cl.account_id = '47F74C65BA8BB02DE053BC010B0AF714'*/
        ) AS MaxDate 
    INNER JOIN 
     (SELECT ESRA.healthfleet_member_id
      , ESRA.Sponsor
      , ESRA.uhc_region
      , ESRA.uhc_major_market
      , ESRA.uhc_minor_market AS ESRA_UHC_MINOR_MARKET
      , ESRA.policy_number AS ESRA_POLICY_NBR
      , ESRA.relationship_code
      , sf.Market_Segment
      , sf.UHC_Minor_Market   AS SF_UHC_MINOR_MARKET
      , sf.CLIENT_NAME
      , sf.Client_Owner
      , sf.Payer_Effective_Date
    FROM reporting_adhoc.ZZ_ESRA_People ESRA
     LEFT JOIN reporting_adhoc.ZZ_Salesforce sf
      ON sf.Policy_Number = ESRA.policy_number
    /*WHERE ESRA.healthfleet_member_id='47F74C65BA8BB02DE053BC010B0AF714'*/
    GROUP BY ESRA.healthfleet_member_id
    , ESRA.Sponsor, ESRA.uhc_region
    , ESRA.uhc_major_market
    , ESRA.uhc_minor_market
    , ESRA.relationship_code
    , sf.Market_Segment
    , sf.UHC_Minor_Market
    , sf.CLIENT_NAME
    , sf.Client_Owner, ESRA.policy_number
    , sf.Payer_Effective_Date
    ) ESRA_DATA 

    ON MaxDate.ACCOUNT_ID = pd.ACCOUNT_ID
    AND ESRA_DATA.healthfleet_member_id = pd.ACCOUNT_ID

    /*WHERE pd.account_id = '47F74C65BA8BB02DE053BC010B0AF714'* --with this 
    it works fine */ 
    WHERE pd.account_id > 0 --an attempt with getting all account_ids

    GROUP BY
    pd.ACCOUNT_ID                 
    , do.MEMBER_NAME  
    , do.GENDER
    , MaxDate.EarlyDATE
    , MaxDate.LateDATE
    ... rest of fields
    ORDER BY pd.ACCOUNT_ID

Answer 1

Without access to your tables and data we probably cannot propose a definite answer, and as your query has missing pieces it makes it even more of a guessing game.

Let's start here:

SELECT DISTINCT  ---------- bad bad
      pd.ACCOUNT_ID
    , MaxDate.EarlyDATE AS ACTIVITY_DATE
    , MaxDate.LateDATE  AS LAST_ACTIVITY_DATE

FROM ZZ_Program_Details pd
LEFT JOIN ZZ_Demographics do ON do.ACCOUNT_ID = pd.ACCOUNT_ID
INNER JOIN (
      SELECT DISTINCT  ---------- bad bad
            cl.ACCOUNT_ID
          , MIN(cl.activity_date) AS EarlyDATE
          , cl1.activity_date     AS LateDATE
      FROM ZZ_Class_Attendance cl
      INNER JOIN ZZ_Class_Attendance cl1 ON cl1.account_id = cl.ACCOUNT_ID
      WHERE cl1.ACTIVITY_DATE > cl.ACTIVITY_DATE

      -- no GROUP BY, so how do your get MIN() or MAX() ?

   ) AS MaxDate ON 
                    ( WHAT???? )

Start by breaking your big query into smaller chunks, like you see above. Does this partial query work? It may not because you have tried to use DISTINCT instead of GROUP BY and the join conditions are unknown as well.

Also note that you have used an INNER JOIN. If that subquery MaxDate fails to return any matching rows then the overall query will won't return rows either. Reconsider each INNER JOIN (perhaps try them as LEFT JOINS).

Once you get that partial query working, add the next join and add the relevant columns to the top select clause. Work on this part of the query until it succeeds, move to the next and repeat until you have a working query.

Do please try to avoid use of "select distinct" in complex queries involving multiple joins. This is not good practice.

---

You might find these queries will help you decide which tables (or subqueries) need left joins or not:

SELECT COUNT(distinct pd.ACCOUNT_ID) acct_c, COUNT(*) row_c
FROM ZZ_Program_Details pd 
;

SELECT COUNT(distinct pd.ACCOUNT_ID) acct_c, COUNT(*) row_c
FROM ZZ_Program_Details pd 
INNER JOIN ZZ_Demographics do ON pd.ACCOUNT_ID = do.ACCOUNT_ID
;

SELECT COUNT(distinct pd.ACCOUNT_ID) acct_c, COUNT(*) row_c
FROM ZZ_Program_Details pd 
LEFT JOIN ZZ_Demographics do ON pd.ACCOUNT_ID = do.ACCOUNT_ID
;

SELECT COUNT(distinct pd.ACCOUNT_ID) acct_c, COUNT(*) row_c
FROM ZZ_Program_Details pd 
INNER JOIN ZZ_Class_Attendance cl ON pd.ACCOUNT_ID = cl.ACCOUNT_ID
INNER JOIN ZZ_Class_Attendance cl1 ON cl1.account_id = cl.ACCOUNT_ID 
;

SELECT COUNT(distinct pd.ACCOUNT_ID) acct_c, COUNT(*) row_c
FROM ZZ_Program_Details pd 
LEFT JOIN ZZ_Class_Attendance cl ON pd.ACCOUNT_ID = cl.ACCOUNT_ID
LEFT JOIN ZZ_Class_Attendance cl1 ON cl1.account_id = cl.ACCOUNT_ID 
;

SELECT COUNT(distinct pd.ACCOUNT_ID) acct_c, COUNT(*) row_c
FROM ZZ_Program_Details pd 
INNER JOIN reporting_adhoc.ZZ_ESRA_People ESRA ON pd.ACCOUNT_ID = ESRA.healthfleet_member_id
INNER JOIN reporting_adhoc.ZZ_Salesforce sf ON sf.Policy_Number = ESRA.policy_number
;

SELECT COUNT(distinct pd.ACCOUNT_ID) acct_c, COUNT(*) row_c
FROM ZZ_Program_Details pd 
LEFT JOIN reporting_adhoc.ZZ_ESRA_People ESRA ON pd.ACCOUNT_ID = ESRA.healthfleet_member_id
LEFT JOIN reporting_adhoc.ZZ_Salesforce sf ON sf.Policy_Number = ESRA.policy_number
;