Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2011
    Posts
    4

    Unanswered: problem with Outer join with 3 tables

    Hi all

    I am not getting required result from a query left outer join on 3 table.
    let me describe what are tables -
    I have three tables
    1. Months (that contain twelve rows for twelve months)
    2. Montly_Data (contains user's monthly contributions(1 row for each month))
    3. Master table (contain user's Master details, father name, dob etc..)

    what i need to retrive is all user's contributions rows month wise(there should 12 rows for each user even there is no row in data table.) there is condition - particular financial-year and company's users should be included in result.

    i have written following query -

    SELECT ROUND(isnull(M.serial,0),0) [cnt],
    isnull(M.monName,'') as [MONTHNM],
    ISNULL(EPSbasic,0) [EPFBasic],
    ISNULL(USERNAME,'') [USERNAME],
    isnull(PFANO,'') [PFANO],
    isnull(FatherName,'') [fathername],
    isnull(dom,'') [DOM],
    ISNULL(dol,'') [DOL],
    isnull(pencont,0) [PenCont],
    isnull(ROI,0) [ROI]
    from EPF_MST_MONTH m left outer join EPF_USER_DATA d
    ON M.cmonth=D.cmonth AND (D.finyear='2011-2012' and
    D.empcode in ('FG0000658-D','FG0000664-D') AND D.CID='DEMO') or (d.empcode is null)
    LEFT OUTER JOIN TDMS_MST_USERS U ON d.empcode = U.EmpCode
    AND U.CID='DEMO' AND U.empcode in ('FG0000658-D','FG0000664-D')

    Thanks in advance and any help on above issue is greately appreciated!

  2. #2
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    You have three tables, and aliases are used in the following manner:

    m left outer join d left outer join u. The last outer join requires data present for d, and if you look at your execution plan you'll see that you in fact have m inner join d left outer join u (the query optimizer is quite smart).

    If you change your query to m left outer join d full outer join u, and perhaps change your join conditions, you should get your intended result.
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  3. #3
    Join Date
    Jul 2011
    Posts
    4

    problem with Outer join with 3 tables

    Thanks Roac for quick reply!

    I tried what you suggested and still not getting required result.

    here i shortened the query from three to two tables.

    SELECT ROUND(isnull(M.serial,0),0) [cnt],
    isnull(M.monName,'') as [MONTHNM],
    ISNULL(EPSbasic,0) [EPFBasic],
    isnull(pencont,0) [PenCont],
    isnull(ROI,0) [ROI]
    from EPF_MST_MONTH M left outer join EPF_USER_DATA D
    ON M.cmonth=D.cmonth
    And D.finyear='2011-2012'
    And D.empcode in ('FG0000658-D','FG0000664-D')
    And D.CID='DEMO'

    above quiery should return 24 rows because 2 employees * 12 months.
    but it's returning 15 rows only.

    what i m doing wrong!!!!

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    You must first use a CROSS JOIN of Months and those two Users.

    On that result set you must LEFT OUTER JOIN the DATA table to add the DATA columns that you want.

    The CROSS JOIN gives you 12 * 2 records and the LEFT OUTER JOIN will not change that number. It will only fill in those columns for which there was a match.
    Code:
    SELECT MA.Name, M.MonthName, D.finYear, D.SomeData
    FROM #Month as M
    	CROSS JOIN #Master as MA
    	LEFT OUTER JOIN #Data as D On
    		MA.empcode = D.empcode AND
    		M.CMonth = D.CMonth
    		AND D.finYear = '2011-2012' 
    WHERE MA.empcode in ('FG0000658-D','FG0000664-D')
    ORDER BY MA.Name, M.CMonth
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Jul 2011
    Posts
    4

    Thumbs up worked like charm...

    Thanks a lot Wim.

    It worked like a charm.... you genius...

Tags for this Thread

Posting Permissions

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