Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2006
    Posts
    110

    Question Unanswered: Subquery in Union query returning same data in multiple rows

    Hi guys,

    I have a complicated (to me atleast) union query that I use for a report that combines my 2 queries. I am having a problem with it, I noticed that all rows in the dt_emailed column have the same date. This should be getting the date_emailed for that specific user and location_code.

    I can't seem to figure out what's going wrong. Any guidance would be most helpful. Thanks!

    Here is my code:

    SELECT EXPIRED.USERID, EXPIRED.USERNAME,EXPIRED.LOCATION_CODE, EXPIRED.LOCATION, EXPIRED.CREATE_DATE, EXPIRED.LAST_LOGIN,DATE_USER_NOTIFIED,USERS.USER_E MAIL_ADDRESS,USERS.FACILITY_ADDRESS, (SELECT COUNT (USERID) FROM USERS WHERE USERID = EXPIRED.USERID AND USERS.ACTIVE = TRUE) AS TTLACCOUNTS,

    (SELECT TOP 1 DATE_EMAILED FROM EMAIL_LOG,EXPIRED WHERE EMAIL_LOG.USER = EXPIRED.USERID AND EXPIRED.LOCATION_CODE = EMAIL_LOG.LOCATION_CODE AND EMAIL_ACTION = 'REQUEST ACCOUNT STATUS') AS DT_EMAILED

    FROM EXPIRED,USERS WHERE EXPIRED.USERID = USERS.USERID AND EXPIRED.LOCATION_CODE = USERS.FACILITY_LIC_NO AND USERS.USER_STATUS = 'COMPLETE (ACTIVE)' AND USERS.ACTIVE = TRUE AND DATEDIFF('D',DATE_USER_NOTIFIED,CDATE(LAST_LOGIN)) >60 AND LAST_LOGIN <> 'NEVER LOGGED IN' AND USERS.USERID IN (SELECT EMAIL_LOG.USER FROM EMAIL_LOG,USERS WHERE EMAIL_ACTION = 'REQUEST ACCOUNT STATUS' AND DATEDIFF('D',EMAIL_LOG.DATE_EMAILED,DATE()) < 60 AND EMAIL_LOG.USER = USERS.USERID
    AND EMAIL_LOG.LOCATION_CODE = USERS.FACILITY_LIC_NO)

    UNION ALL

    SELECT EXPIRED.USERID, EXPIRED.USERNAME,EXPIRED.LOCATION_CODE,EXPIRED.LOC ATION, EXPIRED.CREATE_DATE, EXPIRED.LAST_LOGIN,DATE_USER_NOTIFIED,USERS.USER_E MAIL_ADDRESS,USERS.FACILITY_ADDRESS, (SELECT COUNT (USERID) FROM USERS WHERE USERID = EXPIRED.USERID AND USERS.ACTIVE = TRUE) AS TTLACCOUNTS,

    (SELECT TOP 1 DATE_EMAILED FROM EMAIL_LOG,EXPIRED WHERE EMAIL_LOG.USER = EXPIRED.USERID AND EXPIRED.LOCATION_CODE = EMAIL_LOG.LOCATION_CODE AND EMAIL_ACTION = 'REQUEST ACCOUNT STATUS') AS DT_EMAILED
    FROM EXPIRED,USERS WHERE EXPIRED.USERID = USERS.USERID AND EXPIRED.LOCATION_CODE = USERS.FACILITY_LIC_NO AND USERS.USER_STATUS = 'COMPLETE (ACTIVE)' AND USERS.ACTIVE = TRUE AND LAST_LOGIN = 'NEVER LOGGED IN' AND DATEDIFF('D',DATE_USER_NOTIFIED,DATE()) > 60 AND USERS.USERID IN (SELECT EMAIL_LOG.USER FROM EMAIL_LOG,USERS WHERE EMAIL_ACTION = 'REQUEST ACCOUNT STATUS' AND DATEDIFF('D',EMAIL_LOG.DATE_EMAILED,DATE()) < 60 AND EMAIL_LOG.USER = USERS.USERID
    AND EMAIL_LOG.LOCATION_CODE = USERS.FACILITY_LIC_NO)
    ORDER BY EXPIRED.USERID ASC
    Last edited by cmays637; 09-04-12 at 14:26.

  2. #2
    Join Date
    Oct 2006
    Posts
    110
    I found my problem after more digging and wanted to share my resolution.

    My

    (SELECT TOP 1 DATE_EMAILED FROM EMAIL_LOG,EXPIRED WHERE EMAIL_LOG.USER = EXPIRED.USERID AND EXPIRED.LOCATION_CODE = EMAIL_LOG.LOCATION_CODE AND EMAIL_ACTION = 'REQUEST ACCOUNT STATUS') AS DT_EMAILED

    was changed to

    (SELECT TOP 1 DATE_EMAILED FROM EMAIL_LOG WHERE EMAIL_LOG.USER = EXPIRED.USERID AND EXPIRED.LOCATION_CODE = EMAIL_LOG.LOCATION_CODE AND EMAIL_ACTION = 'REQUEST ACCOUNT STATUS') AS DT_EMAILED


    Removing the ", expired" table from the subquery. I fixed this in both identical subqueries and that made the difference. I figured since it was a subquery you'd need that table included in the statement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •