JOIN two Tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

JOIN two Tables

Author

Topic

ben_53
Yak Posting Veteran

67 Posts

Posted - 2011-07-26 : 14:20:18

Hi guys,

This might be very simple but I am not getting what I want.

I have 2 tables with equal number of same columns (8)
Also they both have same number of rows (500) However they have different rows populated.(200 and 300).
so 300 null for tableA and 200 null for tableB.

I want to put the data in the third table with equal number of same columns which is fully populated (500 rows) (no null values)

Kindly write a query.

I tried CTE, union all, but its giving me 1000 rows. I want 500 with no nulls.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-26 : 14:43:28

You can use a UNION with a where clause or a full join with a where clause. See this example:

CREATE TABLE #tmp1 (id INT);
CREATE TABLE #tmp2 (id INT);
INSERT INTO #tmp1 VALUES (1),(NULL);
INSERT INTO #tmp2 VALUES (3),(NULL);

-- Union with where clause
SELECT * FROM #tmp1 WHERE id IS NOT null
union 
SELECT * FROM #tmp2 WHERE id IS NOT NULL

-- coalesce with where clause
SELECT 
	COALESCE(t1.id,t2.id) AS Id
FROM
	#tmp1 t1 FULL JOIN #tmp2 t2 ON t1.id = t2.id
WHERE
	COALESCE(t1.id,t2.id) IS NOT NULL;

DROP TABLE #tmp1;
DROP TABLE #tmp2;

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-07-26 : 15:09:23

Every SQL forum or newsgroup expects that you will post DDL, sample data and clear specifications which might might include output. This is explained in the FAQ section. We can not read minds; what would we need to know to do your job for you?

Tables must have keys and should have DRI, constraints, and all the basic features of a schema. You should know use ISO-8601 Standards for temporal data, avoid needless dialect, basic data modeling and use ISO-11179 Standards for data element names. Please tell us if you can change the DDL.

>> I have 2 tables with equal number of same columns (8) <<

Keys? Constraints? Anything!!

>> Also they both have same number of rows (500) However they have different rows populated.(200 and 300). so 300 NULL for TableA and 200 NULL for TableB. I want to put the data in the third table with equal number of same columns which is fully populated (500 rows) (no NULL values) <<

What does that mean? Do both tables use the same key? Do you want to COALESCE () columns only if your have non-NULL values in all the non-key columns? Try again, with a good spec, please.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

ben_53
Yak Posting Veteran

67 Posts

Posted - 2011-07-26 : 15:16:40

Thanks sunita

I am using Union query but I am not able to update table3.
error: Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

I can get the data by select statement but not able to update the same. I used:

Update table3
set col1 = (select * from tableA
union
select * from tableB)

like wise I have to set all columns. Its not efficient. Please help

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-26 : 17:55:17

I didn't quite follow your requirement, but in general you can do something like this:


update table3 set
   col1 = s.col1,
   col2 = s.col2,
   col3 = s.col3
--select *
from
	table3 
	inner join
	(
	   select * from tableA
	   union
	   select * from tableB
	)s on s.join_column = table3.join_column

Two things I want to point out that I have highlighted in red:

1. You need some column(s) to relate table3 to tableA and tableB. I called it "join_column".

2. You can remove the parts above the select statement and run it to see what the new values and the old values will be. If you are satisfied, then you can run the update instead of the select.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources