Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2013
    Posts
    46

    Unanswered: How to show all the accounts even if they dont have records for this period.

    I have three tables Accounts, History and Dates . What I need to do is display all the accounts from History (900) records and compare them to the accounts in Accounts table pull all the matching records based on a certain date range , but If there is no record in the History table for this period I still need to display the account from Accounts and some text saying that there is no record matching for this period.
    Account History
    1 1
    2 2
    3 3
    4 NO information for this month
    5 5

    SELECT C.ACCOUNT, CASE WHEN C.ACCOUNT = LEFT(H.NUMBER,8)
    THEN LEFT(H.NUMBER,8) END FROM ACCTS C
    LEFT OUTER JOIN HISTORY H ON C.ACCOUNT = LEFT(H.NUMBER,8)
    INNER JOIN DATES D ON h.PERIOD = D.CUR_PERIOD
    GROUP BY C.ACCOUNT, H.NUMBER

    This will give me all the matching records for the period but I need somehow to show all the accounts even if they don't have records for this period.

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    take a look at coalesce and values clauses. also, why the case statement on a join condition that you have already performed?

  3. #3
    Join Date
    Feb 2013
    Posts
    46
    I am using case because the number in my history table is 13 characters but I need to match only the first 8 characters which is working fine. I can't figure out how to show all the accounts, because when I select by date range I don't have activities for all the records for this period but I still need to show all the accounts.

  4. #4
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Code:
    SELECT 
    	C.ACCOUNT
    	,COALESCE(LEFT(H.NUMBER,8),'No History for this Month') 
    FROM ACCTS C 
    INNER JOIN DATES D 
    	ON h.PERIOD = D.CUR_PERIOD
    LEFT OUTER JOIN HISTORY H 
    	ON C.ACCOUNT = LEFT(H.NUMBER,8)
    GROUP BY 
    	C.ACCOUNT, H.NUMBER
    Last edited by Enigma; 08-05-13 at 14:15. Reason: correcting query
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  5. #5
    Join Date
    Feb 2013
    Posts
    46
    Thanks Enigma, but this is still pulling only the records for the period which my query is doing. What is happening is that I have lets say 900 records in my Accounts table and in my History table I might have data for only 250 records for that particular month. I need to show all the 900 accounts even if there is no data for them for this month.

  6. #6
    Join Date
    Feb 2013
    Posts
    46
    My bad, It works thanks! Forgot to switch DB.

Posting Permissions

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