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!

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.

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!!!!

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.

Thanks a lot Wim.

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