Post reply

LEFT JOINS AND COUNTS

  • December 11, 2012 at 3:51 pm

    #267916

    I have created a query that pulls information from the main table (HSTL_START), and the then through a series of LEFT JOINs and COUNTs displays the number of records in other tables that have the same ID. The code I wrote is shown below.

    The code works great as written below, however if I add another COUNT and JOIN

    COUNT(HSTL_POST_SURVEY.ID) as POST

    LEFT JOIN HSTL_POST_SURVEY ON HSTL_START.ID = HSTL_POST_SURVEY.ID

    Then the counts for the new POST column as well as the count for the PRE column come out wrong.

    Please help!

    My Code

    USE HSTL

    SELECT HSTL_START.ID, HSTL_START.APPROVED_START, HSTL_START.APPROVED_END, HSTL_FINAL_AAR.Actual_Start, HSTL_FINAL_AAR.Actual_End,HSTL_START.MOD1,HSTL_START.MOD2,HSTL_START.MOD3,HSTL_START.MOD4,HSTL_START.MOD5,

    HSTL_START.MOD6,HSTL_START.MOD7,HSTL_START.CI2C_Support_Other,HSTL_START.CI2C_Support_Other_Fill_in,

    COUNT(DISTINCT HSTL_FINAL_AAR.ID) as AAR,

    COUNT(HSTL_CANCEL.ID) as CANX,

    COUNT(HSTL_PRE_SURVEY.ID) as PRE

    FROM HSTL_START

    LEFT JOIN HSTL_FINAL_AAR ON HSTL_START.ID = HSTL_FINAL_AAR.ID

    LEFT JOIN HSTL_CANCEL ON HSTL_START.ID = HSTL_CANCEL.ID

    LEFT JOIN HSTL_PRE_SURVEY ON HSTL_START.ID = HSTL_PRE_SURVEY.ID

    GROUP BY HSTL_START.ID, HSTL_START.APPROVED_START, HSTL_START.APPROVED_END, HSTL_FINAL_AAR.Actual_Start, HSTL_FINAL_AAR.Actual_End,HSTL_START.MOD1,HSTL_START.MOD2,HSTL_START.MOD3,HSTL_START.MOD4,HSTL_START.MOD5,HSTL_START.MOD6,HSTL_START.MOD7,HSTL_START.CI2C_Support_Other,

    HSTL_START.CI2C_Support_Other_Fill_in

  • December 12, 2012 at 9:37 am

    #1567973

    Once you add another join you are quite likely going to get a different number of rows. That is why your counts changed.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply