Left outer join with 3 tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Left outer join with 3 tables

Author

Topic

sunil_pareek
Starting Member

2 Posts

Posted - 2011-07-02 : 07:55:32

Dear all

I have three tables and i want following fields :
Table 1 : EPF_MST_MONTH (all fields and rows from table. this table contains 12 rows for 12 months)

Table 2 : EPF_USER_DATA (Some fields from table) this table may or may not contain employee monthly data.

Table 3 : TDMS_MST_USERS (some fields like father name, dob etc. of employees in table2)

table1 and table2 have Cmonth as comman field and table2 and table3 have EmpCode comman field...

i have written query as follows -

SELECT ROUND(isnull(M.serial,0),0) [cnt], -- TDMS_MST_month
isnull(M.monName,'') as [MONTHNM], -- TDMS_MST_Month
ISNULL(USERNAME,'') [USERNAME], -- TDMS_MST_USERS
isnull(PFANO,'') [PFANO], -- TDMS_MST_USERS
isnull(FatherName,'') [fathername], -- TDMS_MST_USERS
isnull(dom,'') [DOM], -- TDMS_MST_USERS
ISNULL(dol,'') [DOL], -- TDMS_MST_USERS
ISNULL(EPSbasic,0) [EPFBasic], -- EPF_USER_DATA
isnull(pencont,0) [PenCont], -- EPF_USER_DATA
isnull(ROI,0) [ROI] -- EPF_USER_DATA
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')

in above query i want two employees' full year records i.e.(12 * 2) 24 rows
each employee have 3 rows in empdata table so six rows will be with values other 18 rows will be without values i.e. only month name will appear.)

but it's returning only 15 rows....

thanks in advance.
could someone help me out to get required result....

sp

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-02 : 09:27:12

I stared at this for several minutes, but can't figure out why without seeing the data. Are you able to post the data? Also, check the join condition on the first table to second table. Do you need some extra brackets?


SELECT
   ROUND(ISNULL(M.serial, 0), 0) [cnt],   -- TDMS_MST_month
   ISNULL(M.monName, '') AS [MONTHNM],   -- TDMS_MST_Month
   ISNULL(USERNAME, '') [USERNAME],   -- TDMS_MST_USERS
   ISNULL(PFANO, '') [PFANO],   -- TDMS_MST_USERS
   ISNULL(FatherName, '') [fathername],   -- TDMS_MST_USERS
   ISNULL(dom, '') [DOM],   -- TDMS_MST_USERS
   ISNULL(dol, '') [DOL],   -- TDMS_MST_USERS 
   ISNULL(EPSbasic, 0) [EPFBasic],   -- EPF_USER_DATA
   ISNULL(pencont, 0) [PenCont],   -- EPF_USER_DATA
   ISNULL(ROI, 0) [ROI] -- EPF_USER_DATA
FROM
   EPF_MST_MONTH M
   LEFT OUTER JOIN EPF_USER_DATA D
      ON  M.cmonth = D.cmonth AND (( --- < here?
              D.finyear = '2011-2012'
              AND D.empcode IN ('FG0000658-D', 'FG0000664-D')
              AND D.CID = 'DEMO'
          ) OR (d.empcode IS NULL)) -- < and here?
   LEFT OUTER JOIN TDMS_MST_USERS U
      ON  d.empcode = U.EmpCode AND U.CID = 'DEMO' AND U.empcode IN ('FG0000658-D', 'FG0000664-D')

sunil_pareek
Starting Member

2 Posts

Posted - 2011-07-04 : 03:08:23

Thanks Sunita for reply!

Still it's not getting correct results....

btw i got the solution from some other sources...

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources