Differnet Join Statement

Question

Post reply

Differnet Join Statement

thava

SSCrazy

Points: 2275
More actions
May 9, 2013 at 4:30 am

#275124

Hi guru's
Thanks for your support this is my following situation
DECLARE @a as TABLE(Eid VARCHAR(5),DocNo VARCHAR(5))
DECLARE @b-2 as TABLE(Cid VARCHAR(5),CDocNo VARCHAR(5))
INSERT INTO @a
VALUES
('a','1'), ('a','2'),('a','4'), ('a','6'), ('c','3'), ('b','4'), ('e','5'),
('b','1'), ('b','3'), ('b','6')
INSERT INTO @b-2
VALUES
('a','1'), ('a','2'), ('b','4')
Now i want the result as follows
EidcDocNo
a1
a2
aNULL
aNULL
bNULL
bNULL
b4
bNULL
how did i bring this result
if have tried this one but not successfull
SELECT a.Eid, b.cDocNo FROM @a a
LEFT OUTER JOIN @b-2 b ON A.Eid = b.cid AND a.DocNo=b.CDocNo
ORDER BY a.Eid
but result include with the
cNULL
eNULL
any other idea how to get that result
Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script [/url]

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

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

s_osborne2 SSCertifiable Points: 6726 More actions · Answer 1

SELECT

a.Eid,

b.cDocNo

FROM

@a a

LEFT OUTER JOIN @b-2 b ON A.Eid = b.cid AND a.DocNo=b.CDocNo

WHERE

Eid IN (SELECT Cid FROM @b-2)

ORDER BY

a.Eid

That seems to do the trick, unless i've mis-understood what you're after.

Thanks,

Simon

MCSE: Data Platform
MCSE: Business Intelligence
Follow me on Twitter: @WazzTheBadger
LinkedIn Profile: Simon Osborne

thava SSCrazy Points: 2275 More actions · Answer 2

thanks for your response bro, i tried it works thanks , just make a little bit faster here which will increase the IO opeartion also

SELECT a.Eid, b.cDocNo

FROM @a a

LEFT OUTER JOIN @b-2 b

ON A.Eid = b.cid AND

a.DocNo = b.CDocNo

WHERE EXISTS (SELECT 1 FROM @b-2 c WHERE c.Cid = a.Eid )

ORDER BY

a.Eid

it increases the scan count of the second table 2 and double the logical reads, any other solution bro

Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script [/url]

senor.tamas Grasshopper Points: 15 More actions · Answer 3

try this:

DECLARE @a as TABLE(Eid VARCHAR(5),DocNo VARCHAR(5))

DECLARE @b-2 as TABLE(Cid VARCHAR(5),CDocNo VARCHAR(5))

DECLARE @C as TABLE(Did VARCHAR(5),EDocNo VARCHAR(5))

INSERT INTO @a

VALUES

('a','1'), ('a','2'),('a','4'), ('a','6'), ('c','3'), ('b','4'), ('e','5'),

('b','1'), ('b','3'), ('b','6')

INSERT INTO @b-2

VALUES

('a','1'), ('a','2'), ('b','4')

-- Solution1:

SELECT x.Eid AS Eid

, b.cDocNo AS cDocNo

FROM @b-2 b RIGHT JOIN ( SELECT A.Eid

, A.DocNo

FROM @a a INNER JOIN @b-2 b ON ( A.Eid = b.cid )

GROUP BY A.Eid

, A.DocNo ) x ON ( ( b.Cid = x.Eid )

AND ( b.CDocNo = x.DocNo ))

ORDER BY x.Eid

-- Solution2:

INSERT INTO @C

SELECT A.Eid

, A.DocNo

FROM @a a INNER JOIN @b-2 b ON ( A.Eid = b.cid )

GROUP BY A.Eid

, A.DocNo

SELECT c.Did AS Eid

, b.cDocNo AS cDocNo

FROM @C c LEFT JOIN @b-2 b ON ( ( C.Did = b.cid )

AND ( C.EDocNo = b.CDocNo ))

ORDER BY C.Did