How can I get join not duplicate?

Question

I get join 2 tables but use 3 table. I need not duplicate values. But I try join have duplicate.

Table peopleAll:

pNo    pName
-------------
00001  Sang
00002  Janta
00003  Els
00004  Est
00005  Sam
00006  John
00007  Misan
00008  Wila
00009  light
00010  Smith
00011  Ritpo

Table fTime:

cNo    cDate                     cIn                        cOut
-----------------------------------------------------------------------------------
00001  2012-10-22 00:00:00.000   2012-10-22 07:59:00.000    2012-10-22 20:34:00.000
00002  2012-10-22 00:00:00.000   2012-10-22 12:50:00.000    2012-10-22 19:50:00.000
00003  2012-10-22 00:00:00.000   2012-10-22 12:01:00.000    2012-10-22 20:30:00.000
00004  2012-10-22 00:00:00.000   2012-10-22 07:55:00.000    2012-10-22 20:30:00.000
00005  2012-10-22 00:00:00.000   2012-10-22 07:27:00.000    2012-10-22 20:31:00.000
00010  2012-10-22 00:00:00.000   2012-10-22 07:12:00.000    2012-10-22 20:22:00.000

Table Leave:

lNo    lDate                     lStart                     lStop
-----------------------------------------------------------------------------------
00002  2012-10-22 00:00:00.000   2012-10-22 08:00:00.000    2012-10-22 12:00:00.000
00003  2012-10-22 00:00:00.000   2012-10-22 08:00:00.000    2012-10-22 12:00:00.000
00011  2012-10-22 00:00:00.000   NULL                       NULL

This SQL code:

SELECT lr.lNo, lr.lDate, lr.lStart, lr.lStop 
FROM Leave lr 
Where lr.cStart = '2012-10-22'

UNION ALL

SELECT pa.pNo, ISNULL(tf.cDate, Convert(nvarchar(10),'2012-10-22',114)),tf.cIn, tf.cOut
FROM fTime tf FULL OUTER join peopleAll pa On tf.cNo = pa.pNo AND 
tf.cDate = Convert(nvarchar(10),'2012-10-22',114)

returns this output:

lNo    lDate                     lStart                     lStop
-----------------------------------------------------------------------------------
00001  2012-10-22 00:00:00.000   2012-10-22 07:59:00.000    2012-10-22 20:34:00.000
00002  2012-10-22 00:00:00.000   2012-10-22 08:00:00.000    2012-10-22 12:00:00.000
00002  2012-10-22 00:00:00.000   2012-10-22 12:50:00.000    2012-10-22 19:50:00.000
00003  2012-10-22 00:00:00.000   2012-10-22 08:00:00.000    2012-10-22 12:00:00.000
00003  2012-10-22 00:00:00.000   2012-10-22 12:01:00.000    2012-10-22 20:30:00.000
00004  2012-10-22 00:00:00.000   2012-10-22 07:55:00.000    2012-10-22 20:30:00.000
00005  2012-10-22 00:00:00.000   2012-10-22 07:27:00.000    2012-10-22 20:31:00.000
00006  2012-10-22 00:00:00.000   NULL                       NULL
00007  2012-10-22 00:00:00.000   NULL                       NULL
00008  2012-10-22 00:00:00.000   NULL                       NULL
00009  2012-10-22 00:00:00.000   NULL                       NULL
00010  2012-10-22 00:00:00.000   2012-10-22 07:12:00.000    2012-10-22 20:22:00.000
00011  2012-10-22 00:00:00.000   NULL                       NULL

I try to change FULL OUTER JOIN then LEFT OUTER JOIN and I get output:

lNo    lDate                     lStart                     lStop
-----------------------------------------------------------------------------------
00002  2012-10-22 00:00:00.000   2012-10-22 08:00:00.000    2012-10-22 12:00:00.000
00002  2012-10-22 00:00:00.000   2012-10-22 12:50:00.000    2012-10-22 19:50:00.000
00003  2012-10-22 00:00:00.000   2012-10-22 08:00:00.000    2012-10-22 12:00:00.000
00003  2012-10-22 00:00:00.000   2012-10-22 12:01:00.000    2012-10-22 20:30:00.000
00004  2012-10-22 00:00:00.000   2012-10-22 07:55:00.000    2012-10-22 20:30:00.000
00007  2012-10-22 00:00:00.000   NULL                       NULL
00008  2012-10-22 00:00:00.000   NULL                       NULL
00010  2012-10-22 00:00:00.000   2012-10-22 07:12:00.000    2012-10-22 20:22:00.000
00011  2012-10-22 00:00:00.000   NULL                       NULL

LEFT OUTER join have miss some field??

I need this output:

lNo    lDate                     lStart                     lStop
-----------------------------------------------------------------------------------
00001  2012-10-22 00:00:00.000   2012-10-22 07:59:00.000    2012-10-22 20:34:00.000
00002  2012-10-22 00:00:00.000   2012-10-22 12:50:00.000    2012-10-22 19:50:00.000 
00003  2012-10-22 00:00:00.000   2012-10-22 12:01:00.000    2012-10-22 20:30:00.000
00004  2012-10-22 00:00:00.000   2012-10-22 07:55:00.000    2012-10-22 20:30:00.000
00005  2012-10-22 00:00:00.000   2012-10-22 07:27:00.000    2012-10-22 20:31:00.000
00006  2012-10-22 00:00:00.000   NULL                       NULL
00007  2012-10-22 00:00:00.000   NULL                       NULL
00008  2012-10-22 00:00:00.000   NULL                       NULL
00009  2012-10-22 00:00:00.000   NULL                       NULL
00010  2012-10-22 00:00:00.000   2012-10-22 07:12:00.000    2012-10-22 20:22:00.000
00011  2012-10-22 00:00:00.000   NULL                       NULL

(00002,00003 from fTime)

Please help me. Thanks you for you time. :)

score 1 · Accepted Answer · answered Nov 04 '12 at 18:04

Select p.pNo
,Coalesce(l.cDate,t.lDate,'2012-10-22') as ldate
,Coalesce(l.lStart,t.cIn) as lstart
,Coalesce(l.lStop,t.cOut  ) as lstop
from peopleall p
left join leave l on l.lNo=p.Pno and l.ldate='2012-10-22'
left join fTime t on t.tNo=p.Pno and t.cDate='2012-10-22'

score 0 · Answer 2 · 2012-11-04T18:13:30.010

0

Try this

Select p.pNo , 
Coalesce(l.lDate , ftemp.cDate , NULL) as lDate , 
Coalesce(l.lStart,ftemp.cIn , NULL)  as lStart ,
Coalesce(l.lStop , ftemp.cOut, NULL ) as lStop 
from peopleAll p  
left join Leave l  on (p.pNo = l.lNo ) 
left join 
  (select cNo, cDate ,cIn, cOut 
     from  fTime ft where not exists (select 1 from Leave l2 
                                      where ft.cNo = l2.lNo )) 
     ftemp
on (ftemp.cNo = p.pNo )

If you get an error can you send me the out put?

edited Nov 04 '12 at 18:13

answered Nov 04 '12 at 18:07

after try it's out put lNo lDate lStart lStop 00001 2012-10-21 00:00:00.000 2012-10-21 07:00:00.000 2012-10-21 19:00:00.000 00001 2012-10-23 00:00:00.000 2012-10-23 08:00:00.000 2012-10-23 20:00:00.000 00002 2012-10-22 00:00:00.000 2012-10-22 12:50:00.000 2012-10-22 19:50:00.000 00003 2012-10-22 00:00:00.000 2012-10-22 08:00:00.000 2012-10-22 12:00:00.000 00003 2012-10-22 00:00:00.000 2012-10-22 12:01:00.000 2012-10-22 20:30:00.000 ..... ..... ..... it's duplicate value. – nettoon493 Nov 05 '12 at 17:07
1

Would it be too much if you send the result of: select cNo, cDate ,cIn, cOut from fTime ft where not exists (select 1 from Leave l2 where ft.cNo = l2.lNo ) ?? – Nov 05 '12 at 19:59

How can I get join not duplicate?

2 Answers2