March 2, 2010 at 2:13 am
Table A
CREATE TABLE [dbo].[A](
[K] [int] NOT NULL,
[V] [varchar](50) NOT NULL,
CONSTRAINT [PK_A] PRIMARY KEY CLUSTERED
(
[K] ASC,
[V] ASC
)
Data for table A
truncate table A
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
Table B
CREATE TABLE [dbo].(
[K] [int] NOT NULL,
[V] [varchar](50) NOT NULL,
CONSTRAINT [PK_B] PRIMARY KEY CLUSTERED
(
[K] ASC,
[V] ASC
)
Data for table B
truncate table A
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
I need a SQL to get result:
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
Please help me
March 2, 2010 at 5:22 am
I am not sure if I understand your requirement completely, but I guess you are looking for something like this
SELECTa.K, a.V, b.K, b.V
FROM#A a
FULL JOIN #B b ON a.K = b.K AND a.V = b.V
--Ramesh
March 2, 2010 at 5:31 am
Thanks for your reply but your SQL cannot extract this data
2 b 2 null <--A.K=B.K but A.V not in B (with B.K=A.K)
4 b 4 null <--warning
4 null 4 a <--warning
Please help me 🙁
March 2, 2010 at 5:57 am
Let's see if this works for you.
SELECTCOALESCE( c.aK, a1.K ) AS aK, c.aV, COALESCE( c.bK, b1.K ) AS bK, c.bV
FROM(
SELECTa.K AS aK, a.V AS aV, b.K AS bK, b.V AS bV
FROM#A a
FULL JOIN #B b ON a.K = b.K AND a.V = b.V
) c
LEFT JOIN #B b1 ON c.aK = b1.K AND c.bK IS NULL
LEFT JOIN #A a1 ON c.bK = a1.K AND c.aK IS NULL
--Ramesh
March 2, 2010 at 6:00 am
Maybe this one?
SELECT
CASE
WHEN A.K IS NULL AND EXISTS (SELECT * FROM A WHERE A.K = B.K) THEN
B.K
ELSE
A.K
END AK,
A.V,
CASE
WHEN B.K IS NULL AND EXISTS (SELECT * FROM B WHERE B.K = A.K) THEN
A.K
ELSE
B.K
END BK,
B.V
FROM
A
FULL JOIN
B ON A.K = B.K AND A.V = B.V
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply