joins ? - 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 2012 Forums

Transact-SQL (2012)

joins ?

Author

Topic

xclaim
Starting Member

8 Posts

Posted - 2013-12-18 : 17:18:40

trying to get the following output but struggling with it :


output					
site	parent	child	site	parent	child
A	1234	C3348	B	1234	C3348
			B	1234	C9999
A	1234	C3349	B	1234	C3349
A	1234	C3350	B	1234	C3350
A	1234	C3351	B	1234	C3351
A	1234	C3352			
					
data					
site	parent	child			
A	1234	C3348			
A	1234	C3349			
A	1234	C3350			
A	1234	C3351			
A	1234	C3352			
B	1234	C3348			
B	1234	C9999			
B	1234	C3349			
B	1234	C3350			
B	1234	C3351

pls help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-19 : 03:36:32

[code]
SELECT t1.*,
t2.*
FROM Table t1
FULL OUTER JOIN Table t2
ON t2.parent = t1.parent
AND t2.child = t1.child
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

xclaim
Starting Member

8 Posts

Posted - 2013-12-27 : 21:48:12

quote:
Originally posted by visakh16

SELECT t1.*,
t2.*
FROM Table t1
FULL OUTER JOIN Table t2
ON t2.parent = t1.parent
AND t2.child = t1.child

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

thank you this works

xclaim
Starting Member

8 Posts

Posted - 2013-12-27 : 21:53:04

new request as a follow on to the above one ....

here is the sample set


site	parent	child
A	1234	C3348
A	1234	C3349
A	1234	C3350
A	1234	C3351
A	1234	C3352
B	1234	C3348
B	1234	C9999
B	1234	C3349
B	1234	C3350
B	1234	C3351
A	1235	C3322
A	1235	C5567
A	1235	C7789
A	1235	C9987
B	1235	C3322
B	1235	C5567
B	1235	C7789
B	1235	C9987

i need the same results as my first results above i.e
output

				
site	parent	child	site	parent	child
A	1234	C3348	B	1234	C3348
			B	1234	C9999
A	1234	C3349	B	1234	C3349
A	1234	C3350	B	1234	C3350
A	1234	C3351	B	1234	C3351
A	1234	C3352

so the 1235 parent and childs will not need to result because the data-sets are similar in both sites.. i only want to see the deltas.

thanks much.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-28 : 03:16:26

[code]
;With CTE
AS
(
SELECT t1.site AS Asite,
t1.parent AS Aparent,
t1.child AS Achild,
t2.site AS Bsite,
t2.parent AS Bparent,
t2.child AS Bchild
FROM Table t1
FULL OUTER JOIN Table t2
ON t2.parent = t1.parent
AND t2.child = t1.child
)
SELECT Asite,
Aparent,
Achild,
Bsite,
Bparent,
Bchild
FROM
(
SELECT *,SUM(CASE WHEN Asite IS NULL OR Bsite IS NULL THEN 1 ELSE 0 END) OVER (PARTITION BY COALESCE(Aparent,Bparent),COALESCE(Achild,Bchild)) AS Cnt
FROM CTE
)t
WHERE Cnt > 0
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources