Results 1 to 6 of 6

Thread: left join

  1. #1
    Join Date
    Mar 2004
    Posts
    660

    Unanswered: left join

    I have fund field and each fund has different category. When i retrieve the data from particular date. I would like to show all the fund and categories. But it only show the fund and categories with value for the amount field. Is that way to include all the fund and categories. I tried to use left join, it still didn't work. Thanks for your help.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Post the SQL you have so far and we'll see what's what
    George
    Home | Blog

  3. #3
    Join Date
    Mar 2004
    Posts
    660
    The following is the query in sql view. I have four tables. One is fund table, categories table, Receipt table, also i have fundCategories table to contain all the fund with relative categories.


    SELECT tblFundCategories.ID, tblFund.FundAccountingID, tblCategories.AccountingID, Sum(tblSOSReceipt.Amount) AS SumOfAmount, tblReceipt.Categories, tblReceipt.ReceiptDate, tblReceipt.Void
    FROM ((tblReceipt INNER JOIN tblFundCategories ON (tblReceipt.Fund = tblFundCategories.Fund) AND (tblReceipt.Categories = tblFundCategories.Categories)) INNER JOIN tblFund ON tblReceipt.Fund = tblFund.Fund) INNER JOIN tblCategories ON tblReceipt.Categories = tblCategories.Categories
    GROUP BY tblFundCategories.ID, tblFund.FundAccountingID, tblCategories.AccountingID, tblReceipt.Categories, tblReceipt.ReceiptDate, tblReceipt.Void
    HAVING (((tblReceipt.ReceiptDate)=[Enter the date]) AND ((tblReceipt.Void)=No))
    ORDER BY tblFundCategories.ID;

  4. #4
    Join Date
    Mar 2004
    Posts
    660
    Please discard the last message, the following is the code i use.

    SELECT tblFundCategories.ID, tblFundCategories.Fund, tblFundCategories.Categories, Sum(tblReceipt.Amount) AS SumOfAmount, tblReceipt.ReceiptDate, tblCategories.AccountingID, tblFund.FundAccountingID
    FROM ((tblFundCategories LEFT JOIN tblReceipt ON (tblFundCategories.Categories = tblReceipt.Categories) AND (tblFundCategories.Fund = tblReceipt.Fund)) INNER JOIN tblFund ON tblFundCategories.Fund = tblFund.Fund) INNER JOIN tblCategories ON tblFundCategories.Categories = tblCategories.Categories
    GROUP BY tblFundCategories.ID, tblFundCategories.Fund, tblFundCategories.Categories, tblReceipt.ReceiptDate, tblCategories.AccountingID, tblFund.FundAccountingID
    HAVING (((tblReceipt.ReceiptDate)=[Enter the date]))
    ORDER BY tblFundCategories.ID;

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    try like this --
    Code:
    SELECT tblFundCategories.ID
         , tblFundCategories.Fund
         , tblFundCategories.Categories
         , Sum(tblReceipt.Amount) AS SumOfAmount
         , tblReceipt.ReceiptDate
         , tblCategories.AccountingID
         , tblFund.FundAccountingID
      FROM ((
           tblFundCategories 
    INNER 
      JOIN tblFund 
        ON tblFund.Fund             = tblFundCategories.Fund
           ) 
    INNER 
      JOIN tblCategories 
        ON tblCategories.Categories = tblFundCategories.Categories
           )
    LEFT 
      JOIN tblReceipt 
        ON (
           tblReceipt.Categories    = tblFundCategories.Categories
       AND tblReceipt.Fund          = tblFundCategories.Fund
       AND tblReceipt.ReceiptDate = [Enter the date]
           )
    GROUP 
        BY tblFundCategories.ID
         , tblFundCategories.Fund
         , tblFundCategories.Categories
         , tblReceipt.ReceiptDate
         , tblCategories.AccountingID
         , tblFund.FundAccountingID
    ORDER 
        BY tblFundCategories.ID;
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Mar 2004
    Posts
    660
    Thank you so much! You all are expert. It works. I am going to look the code. Thanks again!

Posting Permissions

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