How can I get join not duplicate?

Question

How can I get join not duplicate?

ppc493

SSC Veteran

Points: 290
More actions
November 4, 2012 at 10:50 am

#264240

I get join 2 table but use 3 table. i need not duplicate value. 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 Lirpo
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 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)
get Output from this code
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
So,i try change FULL OUTER join then LEFT OUTER join 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 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. 🙂

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 1

Read the link in my sig (please read this) to see how to generate and post readily-consumable sample data. If folks can get to work on your problem using cut-and-paste sample data, they will be more likely to tackle the problem and you will get a tested solution.

I think you need to spend some time studying joins. Given the information provided so far, your joins should look something like this:

SELECT p.pNo, p.pName, d.*

FROM peopleAll p

LEFT JOIN (

SELECT

lNo_cNo = ISNULL(l.lNo,f.cNo),

l.lNo,l.lDate, l.lStart, l.lStop,

f.cNo, f.cDate, f.cIn, f.cOut

FROM Leave l

FULL OUTER JOIN fTime f ON f.cNo = p.pNo

AND f.fDate = l.lDate

) d ON d.lNo_cNo = p.pNo

ORDER BY p.pNo

Start with table peopleAll, because it contains all of the ID's you are interested in.

The other two tables should be LEFT JOINed to peopleAll, but they also need to be outer joined to each other on ID and date - and the easiest way to understand this is with a derived table, as shown.

Edit: typo.

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

imex Ten Centuries Points: 1234 More actions · Answer 2

Try something like this:

select

pa.pNo,

coalesce(tf.cDate, lr.lDate) as lDate,

coalesce(tf.cIn, lr.LStart) as lStart,

coalesce(tf.cOut, lr.lStop) as lStop

from peopleAll pa

left join fTime tf

on tf.cNo = pa.pNo and tf.cDate = '2012-10-22'

left join Leave lr

on lr.lNo = pa.pNo and lr.lDate = '2012-10-22'

Hope this helps.

https://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos

ppc493 SSC Veteran Points: 290 More actions · Answer 3

ppc493

SSC Veteran

Points: 290

November 5, 2012 at 9:34 pm

#1556288

Rookie thanks so much!!. it's Work 😀