Interesting problem - 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 2005 Forums

Transact-SQL (2005)

Interesting problem

Author

Topic

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-03-02 : 06:58:29

Interesting problem i found in another sql forum and i m not able to get it resolved.

can any body solve this...

GO
IF EXISTS( SELECT NULL FROM sys.objects where OBJECT_ID = OBJECT_ID('A') AND Type = 'U' )
BEGIN
DROP TABLE A
END
GO
CREATE TABLE [dbo].[A](
[K] [int] NOT NULL,

[varchar](50) NOT NULL
)

GO
insert into A
select 1 as K,'a' as V
union all select 1 as K,'b' as V
union all select 2 as K,'a' as V
union all select 2 as K,'b' as V
union all select 4 as K,'b' as V
union all select 5 as K,'c' as V
GO
IF EXISTS( SELECT NULL FROM sys.objects where OBJECT_ID = OBJECT_ID('B') AND Type = 'U' )
BEGIN
DROP TABLE B
END
GO
CREATE TABLE [dbo].[B](
[K] [int] NOT NULL,

[varchar](50) NOT NULL
)
GO
insert into B
select 1 as K,'a' as V
union all select 1 as K,'b' as V
union all select 2 as K,'a' as V
union all select 3 as K,'b' as V
union all select 4 as K,'a' as V
GO

SELECT * FROM A
SELECT * FROM B
-- I want output like below
/*
A.K A.V B.K B.V
1 b 1 b
2 a 2 a
2 b 2 null <--warning
4 b 4 null <--warning
5 c null null
null null 3 b
4 null 4 a <--warning
*/

Vabhav T

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-02 : 07:16:37

looks like you have to use FULL OUTER JOIN

No, you're never too old to Yak'n'Roll if you're too young to die.

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-03-02 : 07:36:58

[code]SELECT COALESCE(A.K, (SELECT DISTINCT A.K FROM A WHERE A.K = B.K), NULL) AS [A.K],
A.V AS [A.V],
COALESCE(B.K, (SELECT DISTINCT B.K FROM B WHERE A.K = B.K), NULL) AS [B.K],
B.V AS [B.V]
FROM A FULL OUTER JOIN B
ON A.K = B.K AND A.V = B.V
/*

A.K A.V B.K B.V
----------- -------------------------------------------------- ----------- --------------------------------------------------
1 a 1 a
1 b 1 b
2 a 2 a
2 b 2 NULL
4 b 4 NULL
5 c NULL NULL
NULL NULL 3 b
4 NULL 4 a
*/

[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources