Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Differnet Join Statement Expand / Collapse
Author
Message
Posted Thursday, May 9, 2013 4:30 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, September 14, 2014 12:30 AM
Points: 253, Visits: 540
Hi guru's
Thanks for your support this is my following situation
DECLARE @A as TABLE(Eid VARCHAR(5),DocNo VARCHAR(5))
DECLARE @B 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
VALUES
('a','1'), ('a','2'), ('b','4')

Now i want the result as follows
Eid cDocNo
a 1
a 2
a NULL
a NULL
b NULL
b NULL
b 4
b NULL

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 b ON A.Eid = b.cid AND a.DocNo=b.CDocNo
ORDER BY a.Eid

but result include with the
c NULL
e NULL
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
Post #1450998
Posted Thursday, May 9, 2013 4:41 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Wednesday, December 3, 2014 8:01 AM
Points: 711, Visits: 2,210
SELECT 
a.Eid,
b.cDocNo
FROM
@A a
LEFT OUTER JOIN @B b ON A.Eid = b.cid AND a.DocNo=b.CDocNo
WHERE
Eid IN (SELECT Cid FROM @B)
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
Post #1451005
Posted Thursday, May 9, 2013 4:52 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, September 14, 2014 12:30 AM
Points: 253, Visits: 540
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 b
ON A.Eid = b.cid AND
a.DocNo = b.CDocNo
WHERE EXISTS (SELECT 1 FROM @b 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
Post #1451016
Posted Friday, May 10, 2013 10:52 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 10, 2013 12:08 PM
Points: 1, Visits: 1
try this:


DECLARE @A as TABLE(Eid VARCHAR(5),DocNo VARCHAR(5))
DECLARE @B 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
VALUES
('a','1'), ('a','2'), ('b','4')

-- Solution1:
SELECT x.Eid AS Eid
, b.cDocNo AS cDocNo
FROM @B b RIGHT JOIN ( SELECT A.Eid
, A.DocNo
FROM @A a INNER JOIN @B 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 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 b ON ( ( C.Did = b.cid )
AND ( C.EDocNo = b.CDocNo ))
ORDER BY C.Did


Post #1451689
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse