473,326 Members | 2,128 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Union Tables without duplicating lines with NULL values

denny1824
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
7 20404
ck9663
2,878 Expert 2GB
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
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
2,878 Expert 2GB
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
1,134 Expert 1GB
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
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
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
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

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

Similar topics

0
by: Neculai Macarie | last post by:
Hi! Using Union and Order By gives strange behaviour in the following test-case: drop table if exists gallery; drop table if exists gallery_categ; # create test tables create table gallery...
3
by: Rich Protzel | last post by:
Hello, So my table contains say 100,000 records, and I need to group the categories in fld1 by the highest count of subcategories. Say fld1 contains categories A, B, C, D, E. All of these...
2
by: Brent Wege | last post by:
Hi I have the following union query that retrieves two counts. Can I sum them up within this query, like wrap this in a sum function somehow to get the total count? Or is there a better way to do...
3
by: Random Person | last post by:
Does anyone know how to use VBA to relink tables between two MS Access databases? We have two databases, one with VBA code and the other with data tables. The tables are referenced by linked...
14
by: Salad | last post by:
A97. Situation: I have 3 tables with a text field in each and a date field in the first 2 tables: Table1 Text1, Date1 Table2 Text2, Date2 Table3 Text3 (no date field) The following...
2
by: Lyn | last post by:
Hi, How do you bind the output columns from a UNION query when the fields from the two tables have different names? Consider this query (WHERE clauses omitted)... SELECT SurnameBirth,...
7
by: urban.widmark | last post by:
Hello We are having some problems with triggers, sequences and union all in V8 on code that worked fine in V7. Was wondering if someone else has seen this and/or knows what to do. A trigger...
7
by: KoliPoki | last post by:
Hello every body. I have a small issue. Problem: I have a table with 4 descriptor columns (type). I need to formulate a query to retrieve a count for each type so I can group by...etc. The...
7
by: jason.langdale | last post by:
I have 3 tables I want to use in a view. Table A has field 1,2,3,4,5 and table B has field 1,2,3,4,5. I want to do a union on these. (I have done so successfully if I stop here) I also want to join...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.