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.
7 20404
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.... -
select
-
-
ID = case when table1.ID is null and table2.ID is not null then table2.ID
-
case when table2.ID is null and table1.ID is not null then table1.ID
-
else table1.ID end,
-
-
Column1 = case when table1.ID is null and table2.ID is not null then table2.Column1
-
case when table2.ID is null and table1.ID is not null then table1.Column1
-
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
-
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
-
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
-
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
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
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
you can simplify all of that like this.
assuming you are using MS SQL server -
select isnull(table1.ID,table2.ID) as ID,
-
isnull(table1.Column1,table2.Column1) as Column1
-
from table1
-
full outer join table2 on table1.ID = table2.ID end
-
and using your orriginal query you could do this -
SELECT ID,max(Column1) as Column1
-
FROM
-
(
-
SELECT ID,Column1 FROM Table1
-
UNION
-
SELECT ID,Column1 FROM Table2
-
)a
-
GROUP BY ID
-
you can simplify all of that like this.
assuming you are using MS SQL server -
select isnull(table1.ID,table2.ID) as ID,
-
isnull(table1.Column1,table2.Column1) as Column1
-
from table1
-
full outer join table2 on table1.ID = table2.ID end
-
Both of these ways work. I will use Delerna's way because I think it is simpler.
Thank You Both CK and Delerna
Denny
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: -
CREATE TABLE #Table1
-
(
-
ID int,
-
Column1 nvarchar(50),
-
Column2 nvarchar(50),
-
Column3 nvarchar(50)
-
)
-
-
CREATE TABLE #Table2
-
(
-
ID int,
-
Column1 nvarchar(50),
-
Column2 nvarchar(50),
-
Column3 nvarchar(50)
-
)
-
-
INSERT INTO #Table1 VALUES (1, 'Value1', 'Value2', 'Value31')
-
INSERT INTO #Table1 VALUES (2, 'Value1', 'Value2', 'Value32')
-
INSERT INTO #Table1 VALUES (3, NULL, NULL, 'Value33')
-
INSERT INTO #Table1 VALUES (4, NULL, NULL, 'Value34')
-
INSERT INTO #Table1 VALUES (5, 'Value1', NULL, 'Value35')
-
-
INSERT INTO #Table2 VALUES (1, 'Value1', 'Value2', 'Value31')
-
INSERT INTO #Table2 VALUES (2, NULL, NULL, 'Value32')
-
INSERT INTO #Table2 VALUES (3, 'Value1', 'Value2', 'Value33')
-
INSERT INTO #Table2 VALUES (4, NULL, NULL, 'Value34')
-
INSERT INTO #Table2 VALUES (5, 'Value1', 'Value2', 'Value35')
-
-
SELECT isnull(#Table1.ID,#Table2.ID) AS ID,
-
isnull(#Table1.Column1,#Table2.Column1) AS Column1,
-
isnull(#Table1.Column2,#Table2.Column2) AS Column2,
-
isnull(#Table1.Column3,#Table2.Column3) AS Column3
-
FROM #Table1 full OUTER JOIN #Table2 ON #Table1.ID = #Table2.ID
-
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
I discovered that the values of Column2 in #Table1 are always null.
Here is my current solution: -
CREATE TABLE #Table1
-
(
-
ID int,
-
Column1 nvarchar(50),
-
Column2 nvarchar(50),
-
Column3 nvarchar(50)
-
)
-
-
CREATE TABLE #Table2
-
(
-
ID int,
-
Column1 nvarchar(50),
-
Column2 nvarchar(50),
-
Column3 nvarchar(50)
-
)
-
-
INSERT INTO #Table1 VALUES (1, 'Value1', NULL, 'Value31')
-
INSERT INTO #Table1 VALUES (2, 'Value1', NULL, 'Value32')
-
INSERT INTO #Table1 VALUES (3, NULL, NULL, 'Value33')
-
INSERT INTO #Table1 VALUES (4, NULL, NULL, 'Value34')
-
INSERT INTO #Table1 VALUES (5, 'Value1', NULL, 'Value35')
-
-
INSERT INTO #Table2 VALUES (1, 'Value1', 'Value2', 'Value31')
-
INSERT INTO #Table2 VALUES (2, NULL, NULL, 'Value32')
-
INSERT INTO #Table2 VALUES (3, 'Value1', 'Value2', 'Value33')
-
INSERT INTO #Table2 VALUES (4, NULL, NULL, 'Value34')
-
INSERT INTO #Table2 VALUES (5, 'Value1', 'Value2', 'Value35')
-
-
DECLARE @TempResults1 TABLE
-
(
-
ID int,
-
Column1 nvarchar(50),
-
Column2 nvarchar(50),
-
Column3 nvarchar(50)
-
)
-
-
DECLARE @TempResults2 TABLE
-
(
-
ID int,
-
Column1 nvarchar(50),
-
Column2 nvarchar(50),
-
Column3 nvarchar(50)
-
)
-
-
INSERT INTO @TempResults1
-
(
-
ID, Column1, Column2, Column3
-
)
-
(
-
SELECT isnull(#Table1.ID,#Table2.ID) AS ID,
-
isnull(#Table1.Column1,#Table2.Column1) AS Column1,
-
isnull(#Table1.Column2,#Table2.Column2) AS Column2,
-
isnull(#Table1.Column3,#Table2.Column3) AS Column3
-
FROM #Table1 full OUTER JOIN #Table2 ON #Table1.ID = #Table2.ID
-
)
-
-
INSERT INTO @TempResults2
-
(
-
ID, Column1, Column2, Column3
-
)
-
(
-
SELECT isnull(#Table1.ID,#Table2.ID) AS ID,
-
isnull(#Table1.Column1,#Table2.Column1) AS Column1,
-
Column2 = case when #Table1.Column1 is null then isnull(#Table2.Column2,#Table1.Column2) end,
-
isnull(#Table1.Column3,#Table2.Column3) AS Column3
-
FROM #Table1 full OUTER JOIN #Table2 ON #Table1.ID = #Table2.ID
-
)
-
-
Select * FROM @TempResults1
-
Union
-
Select * FROM @TempResults2
-
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
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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...
|
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...
|
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...
|
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,...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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....
|
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
|
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...
| |