April 22, 2010 at 10:22 pm
I feel like this should be simple, but I'm stumped...
I have two tables, with a common ID column, with zero or more rows for each ID value in one or both tables.
I need to output the values from the first table along with the values from the second table, with nulls for either the values in the first or second table, depending on how many rows exist for a given ID.
For example, if there are 3 values for a given ID in TableA and 5 values for that ID in TableB, the result set should have the 3 values for TableA in the first 3 rows and nulls in the last two rows. The 5 values for TableB will be in the rows. The reverse would be true if TableA had more values for a given ID than TableB.
To illustrate:
TableA
-------
ID | TableA_Value
1 | A1
1 | A2
1 | A3
TableB
-------
ID | TableB_Value
1 | B1
1 | B2
1 | B3
1 | B4
1 | B5
I need a query that will give:
ID | TableA_Value | TableB_Value
1 | A1 | B1
1 | A2 | B2
1 | A3 | B3
1 | NULL | B4
1 | NULL | B5
NULL's are on the left for the last two rows becuase TableB has more rows for ID value 1 than TableA.
But if the tables were like this:
TableA
-------
ID | TableA_Value
1 | A1
1 | A2
1 | A3
1 | A4
1 | A5
TableB
------
ID | TableB_Value
1 | B1
1 | B2
I would want:
ID | TableA_Value | TableB_Value
1 | A1 | B1
1 | A2 | B2
1 | A3 | NULL
1 | A4 | NULL
1 | A5 | NULL
Now the NULLs are on the TableB side since TableA has more values for ID 1 than TableB.
I tried something like:
DECLARE @TableA TABLE (
RowID INT NOT NULL,
DataValueA VARCHAR(20)
)
DECLARE @TableB TABLE (
RowID INT NOT NULL,
DataValueB VARCHAR(20)
)
INSERT INTO @TableA VALUES (1,'A1')
INSERT INTO @TableA VALUES (1,'A2')
INSERT INTO @TableA VALUES (1,'A3')
INSERT INTO @TableB VALUES (1,'B1')
INSERT INTO @TableB VALUES (1,'B2')
INSERT INTO @TableB VALUES (1,'B3')
INSERT INTO @TableB VALUES (1,'B4')
INSERT INTO @TableB VALUES (1,'B5')
SELECT a.RowID, a.DataValueA, b.RowID, b.DataValueB
FROM @TableA a
FULL OUTER JOIN @TableB b ON b.RowID = a.RowID
ORDER BY a.RowID, a.DataValue
but this gives the values in TableB for every value in TableA.
Where am I going wrong here?
Thanks!
April 22, 2010 at 10:45 pm
Hi,
I think your query should be like this...
Select * from (
SELECT a.RowID A_RowID, a.DataValueA, b.RowID B_RowID, b.DataValueB
FROM @TableA a
LEFT OUTER JOIN @TableB b ON b.RowID = a.RowID and b.DataValueB = a.DataValueA
Union all
SELECT a.RowID A_RowID, a.DataValueA, b.RowID B_RowID, b.DataValueB
FROM @TableA a
Right OUTER JOIN @TableB b ON b.RowID = a.RowID and b.DataValueB = a.DataValueA
) Main
ORDER BY A_RowID, DataValueA
Atif SHeikh
April 22, 2010 at 11:23 pm
This should work for you
SELECTCOALESCE( A.RowID, B.RowID ) RowID, DataValueA, DataValueB
FROM(
SELECTROW_NUMBER() OVER ( PARTITION BY RowID ORDER BY DataValueA ) RowNum, *
FROM@TableA
) A
FULL OUTER JOIN(
SELECTROW_NUMBER() OVER ( PARTITION BY RowID ORDER BY DataValueB ) RowNum, *
FROM@TableB
) B ON A.RowNum = B.RowNum AND A.RowID = B.RowID
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 22, 2010 at 11:36 pm
Oh. Mistake in my query. I assumed that the Values of both tables will be same.
This was my test script...
Declare @vTable1 Table (id int, descr varchar(10))
Declare @vTable2 Table (id int, descr varchar(10))
Insert into @vTable1
Select 1,'A' union all
Select 1,'B' union all
Select 1,'C' union all
Select 1,'D'
Insert into @vTable2
Select 1,'A' union all
Select 1,'B' union all
Select 1,'C' union all
Select 1,'D' union all
Select 1,'E'
Select Distinct id,Descr,Descr2 from (
Select Distinct a.id,a.Descr Descr, b.Descr as Descr2 from @vTable1 a
Left outer join @vTable2 b on b.id = a.id and b.descr = a.descr
Union All
Select Distinct a.id,a.Descr Descr, b.Descr as Descr2 from @vTable1 a
Right outer join @vTable2 b on b.id = a.id and b.descr = a.descr
) Main Order by Descr,Descr2
April 22, 2010 at 11:50 pm
New version of my query,
Select Distinct * from (
SELECT a.RowID A_RowID, a.DataValueA, b.RowID B_RowID, b.DataValueB
FROM (Select *,Row_Number() over (order by Rowid) as rno from @TableA) a
LEFT OUTER JOIN (Select *,Row_Number() over (order by Rowid) as rno from @TableB) b ON b.RowID = a.RowID and b.rno = a.rno
Union all
SELECT a.RowID A_RowID, a.DataValueA, b.RowID B_RowID, b.DataValueB
FROM (Select *,Row_Number() over (order by Rowid) as rno from @TableA) a
RIGHT OUTER JOIN (Select *,Row_Number() over (order by Rowid) as rno from @TableB) b ON b.RowID = a.RowID and b.rno = a.rno
) Main
ORDER BY A_RowID, DataValueA
April 23, 2010 at 7:00 am
Atif,
That doesn't exactly give what I need because there is no relationship between DataValueA and DataValueB.
The only relationship between the two tables is RowID.
When I run your query, I get:
A_RowID | DataValueA | B_RowID | DataValueB
NULL | NULL | 1 | B1
NULL | NULL | 1 | B2
NULL | NULL | 1 | B3
NULL | NULL | 1 | B4
NULL | NULL | 1 | B5
1 | A1 | NULL | NULL
1 | A2 | NULL | NULL
1 | A3 | NULL | NULL
April 25, 2010 at 10:17 pm
Plz try the last query that i posted. It gives result;
A_RowID DataValueA B_RowID DataValueB
NULL NULL 1 B4
NULL NULL 1 B5
1 A1 1 B1
1 A2 1 B2
1 A3 1 B3
August 5, 2016 at 2:53 am
it works.
thank you.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply