By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,701 Members | 1,782 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,701 IT Pros & Developers. It's quick & easy.

Union Tables without duplicating lines with NULL values

denny1824
P: 32
I want to union 2 tables that have the same columns, but for 1 of the columns, if the column is null in one table, i want the result table to have the value from the other table.

Example:

Table1
ID, Column1
1, Value1
2, Value1
3, NULL
4, NULL

Table2
ID, Column1
1, Value1
2, NULL
3, Value1
4, NULL

DesiredResultTable
ID, Column1
1, Value1
2, Value1
3, Value1
4, NULL


The code I have is like:
SELECT * FROM Table1
UNION
SELECT * FROM Table2

This Results In:
UndesiredResultTable
ID, Column1
1, Value1
2, NULL
2, Value1
3, NULL
3, Value1
4, NULL

This example is a simplification of the problem I am working on. If I am able to do this union, then I will be able to solve my problem.
Jan 30 '08 #1
Share this Question
Share on Google+
7 Replies


ck9663
Expert 2.5K+
P: 2,878
assumption:
1. the column1 of the table either have the same value or the other is null, in this case, you'll take the non-null value. a situation where both column will be non-null but are not the same. otherwise, which one would you choose?

if this assumption is correct, proceed....
Expand|Select|Wrap|Line Numbers
  1. select 
  2.  
  3. ID = case when table1.ID is null and table2.ID is not null then table2.ID
  4.        case when table2.ID is null and table1.ID is not null then table1.ID
  5.        else table1.ID end,
  6.  
  7. Column1 = case when table1.ID is null and table2.ID is not null then table2.Column1
  8. case when table2.ID is null and table1.ID is not null then table1.Column1
  9. case table1.ID is not null and table2.ID is not null and table1.column1 is not null and table2.column1 is null then table1.column1
  10. case table1.ID is not null and table2.ID is not null and table1.column1 is null and table2.column1 is not null then table2.column1
  11. case table1.ID is not null and table2.ID is not null and table1.column1 is null and table2.column1 is null then table1.column1
  12. from table1 full outer join table2 on table1.ID = table2.ID end
note:
i did not check this for syntax, but i hope you're getting what am trying to do here...

-- ck
Jan 30 '08 #2

denny1824
P: 32
CK,

Your assumption, that if Column1 is NOT null in both tables then they will always be equal, IS correct.

ID in both tables will never be NULL. It IS possible that one of the tables may have ID values that are NOT in the other table.

In your code you are checking if ID IS null, but I do NOT think that needs to be checked.

I will look at your code more thoroughly tomorrow morning. Thank You for your reply.

Denny
Jan 30 '08 #3

ck9663
Expert 2.5K+
P: 2,878
CK,

Your assumption, that if Column1 is NOT null in both tables then they will always be equal, IS correct.

ID in both tables will never be NULL. It IS possible that one of the tables may have ID values that are NOT in the other table.

In your code you are checking if ID IS null, but I do NOT think that needs to be checked.

I will look at your code more thoroughly tomorrow morning. Thank You for your reply.

Denny

full outer join returns all the fields of the two tables using the key as matching parameters. if there's a row in table1 that's not in table 2, it will return all records in table1 and all fields in table2 empty (and vice-versa)...the check is to handle those kinds of records wherein the it has no matching on the other table. the fact that there are no ID = NULLs will help this query more...

-- ck
Jan 30 '08 #4

Delerna
Expert 100+
P: 1,134
you can simplify all of that like this.
assuming you are using MS SQL server

Expand|Select|Wrap|Line Numbers
  1. select isnull(table1.ID,table2.ID) as ID,
  2.           isnull(table1.Column1,table2.Column1) as Column1
  3. from table1 
  4. full outer join table2 on table1.ID = table2.ID end
  5.  

and using your orriginal query you could do this

Expand|Select|Wrap|Line Numbers
  1. SELECT ID,max(Column1) as Column1
  2. FROM
  3. (
  4.    SELECT ID,Column1 FROM Table1
  5.    UNION
  6.    SELECT ID,Column1 FROM Table2
  7. )a
  8. GROUP BY ID
  9.  
Jan 31 '08 #5

denny1824
P: 32
you can simplify all of that like this.
assuming you are using MS SQL server

Expand|Select|Wrap|Line Numbers
  1. select isnull(table1.ID,table2.ID) as ID,
  2.           isnull(table1.Column1,table2.Column1) as Column1
  3. from table1 
  4. full outer join table2 on table1.ID = table2.ID end
  5.  
Both of these ways work. I will use Delerna's way because I think it is simpler.

Thank You Both CK and Delerna
Denny
Jan 31 '08 #6

denny1824
P: 32
The code worked like I asked for, but now I have found that there IS another column that is NOT always the same in both tables. If Column1 IS null then Column2 will always be null. If Column1 is NOT null then Column2 may be null or may be NOT null in the 2 tables (if the database was clean then Column2 would always be the same). I am using TSQL.

The example is different than before:

Expand|Select|Wrap|Line Numbers
  1. CREATE TABLE #Table1
  2. (
  3.     ID int,
  4.     Column1 nvarchar(50),
  5.     Column2 nvarchar(50),
  6.     Column3 nvarchar(50)
  7. )
  8.  
  9. CREATE TABLE #Table2
  10. (
  11.     ID int,
  12.     Column1 nvarchar(50),
  13.     Column2 nvarchar(50),
  14.     Column3 nvarchar(50)
  15. )
  16.  
  17. INSERT INTO #Table1 VALUES (1, 'Value1', 'Value2', 'Value31')
  18. INSERT INTO #Table1 VALUES (2, 'Value1', 'Value2', 'Value32')
  19. INSERT INTO #Table1 VALUES (3, NULL, NULL, 'Value33')
  20. INSERT INTO #Table1 VALUES (4, NULL, NULL, 'Value34')
  21. INSERT INTO #Table1 VALUES (5, 'Value1', NULL, 'Value35')
  22.  
  23. INSERT INTO #Table2 VALUES (1, 'Value1', 'Value2', 'Value31')
  24. INSERT INTO #Table2 VALUES (2, NULL, NULL, 'Value32')
  25. INSERT INTO #Table2 VALUES (3, 'Value1', 'Value2', 'Value33')
  26. INSERT INTO #Table2 VALUES (4, NULL, NULL, 'Value34')
  27. INSERT INTO #Table2 VALUES (5, 'Value1', 'Value2', 'Value35')
  28.  
  29. SELECT    isnull(#Table1.ID,#Table2.ID) AS ID,
  30.     isnull(#Table1.Column1,#Table2.Column1) AS Column1,
  31.     isnull(#Table1.Column2,#Table2.Column2) AS Column2,
  32.     isnull(#Table1.Column3,#Table2.Column3) AS Column3
  33. FROM #Table1 full OUTER JOIN #Table2 ON #Table1.ID = #Table2.ID 
  34.  
The Desired Result Table Is:
ID, Column1, Column2, Column3
1, 'Value1', 'Value2', 'Value31'
2, 'Value1', 'Value2', 'Value32'
3, 'Value1', 'Value2', 'Value33'
4, NULL, NULL, 'Value34'
5, 'Value1', NULL, 'Value35'
5, 'Value1', 'Value2', 'Value35'

The Undesired Actual Result Is:
ID, Column1, Column2, Column3
1, 'Value1', 'Value2', 'Value31'
2, 'Value1', 'Value2', 'Value32'
3, 'Value1', 'Value2', 'Value33'
4, NULL, NULL, 'Value34'
5, 'Value1', 'Value2', 'Value35'

Thank You
Denny
Jan 31 '08 #7

denny1824
P: 32
I discovered that the values of Column2 in #Table1 are always null.

Here is my current solution:

Expand|Select|Wrap|Line Numbers
  1. CREATE TABLE #Table1
  2. (
  3.     ID int,
  4.     Column1 nvarchar(50),
  5.     Column2 nvarchar(50),
  6.     Column3 nvarchar(50)
  7. )
  8.  
  9. CREATE TABLE #Table2
  10. (
  11.     ID int,
  12.     Column1 nvarchar(50),
  13.     Column2 nvarchar(50),
  14.     Column3 nvarchar(50)
  15. )
  16.  
  17. INSERT INTO #Table1 VALUES (1, 'Value1', NULL, 'Value31')
  18. INSERT INTO #Table1 VALUES (2, 'Value1', NULL, 'Value32')
  19. INSERT INTO #Table1 VALUES (3, NULL, NULL, 'Value33')
  20. INSERT INTO #Table1 VALUES (4, NULL, NULL, 'Value34')
  21. INSERT INTO #Table1 VALUES (5, 'Value1', NULL, 'Value35')
  22.  
  23. INSERT INTO #Table2 VALUES (1, 'Value1', 'Value2', 'Value31')
  24. INSERT INTO #Table2 VALUES (2, NULL, NULL, 'Value32')
  25. INSERT INTO #Table2 VALUES (3, 'Value1', 'Value2', 'Value33')
  26. INSERT INTO #Table2 VALUES (4, NULL, NULL, 'Value34')
  27. INSERT INTO #Table2 VALUES (5, 'Value1', 'Value2', 'Value35')
  28.  
  29. DECLARE @TempResults1 TABLE
  30. (
  31.     ID int,
  32.     Column1 nvarchar(50),
  33.     Column2 nvarchar(50),
  34.     Column3 nvarchar(50)
  35. )
  36.  
  37. DECLARE @TempResults2 TABLE
  38. (
  39.     ID int,
  40.     Column1 nvarchar(50),
  41.     Column2 nvarchar(50),
  42.     Column3 nvarchar(50)
  43. )
  44.  
  45. INSERT INTO @TempResults1
  46. (
  47.     ID, Column1, Column2, Column3
  48. )
  49. (
  50. SELECT    isnull(#Table1.ID,#Table2.ID) AS ID,
  51.     isnull(#Table1.Column1,#Table2.Column1) AS Column1,
  52.     isnull(#Table1.Column2,#Table2.Column2) AS Column2,
  53.     isnull(#Table1.Column3,#Table2.Column3) AS Column3
  54. FROM #Table1 full OUTER JOIN #Table2 ON #Table1.ID = #Table2.ID 
  55. )
  56.  
  57. INSERT INTO @TempResults2
  58. (
  59.     ID, Column1, Column2, Column3
  60. )
  61. (
  62. SELECT    isnull(#Table1.ID,#Table2.ID) AS ID,
  63.     isnull(#Table1.Column1,#Table2.Column1) AS Column1,
  64.     Column2 = case when #Table1.Column1 is null then isnull(#Table2.Column2,#Table1.Column2) end,
  65.     isnull(#Table1.Column3,#Table2.Column3) AS Column3
  66. FROM #Table1 full OUTER JOIN #Table2 ON #Table1.ID = #Table2.ID 
  67. )
  68.  
  69. Select * FROM @TempResults1 
  70. Union 
  71. Select * FROM @TempResults2
  72.  
If anyone has a better way to do this, then I will be very happy to hear it. If not, then I will be going with this solution.

Thanks Again,
Denny
Jan 31 '08 #8

Post your reply

Sign in to post your reply or Sign up for a free account.