merging 2 tables, no coniditons

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

SQL Server 2000 Forums

Transact-SQL (2000)

merging 2 tables, no coniditons

Author

Topic

dirwin26
Yak Posting Veteran

81 Posts

Posted - 2005-10-03 : 17:36:24

Can anyone tell me how I can create a merged table off of the below 2 tables(1,2):
table1:
TheDate Revenues
Null 10
Null 20
Null 30
Table2:
The Date Revenues
2005-01-01 NULL
2005-01-02 Null
2005-01-03 NULL

result table:
2005-01-01 10
2005-01-02 20
2005-01-03 30

Thanks for the help!
Dave

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-10-03 : 19:10:28

Is there a relationship between these two tables?
How do you identify which rows from table1 match with rows from table2 ?

Nathan Skerl

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-10-03 : 19:51:49

Just curious how I would do it without any unique row identifiers... not that this data means anything

set nocount on

declare @table_1 table (thedate datetime, revenues int)
declare @table_2 table (thedate datetime, revenues int)
insert into @table_1
	select 	null, 10 union
	select 	null, 20 union
	select 	null, 30

insert into @table_2
	select 	'2005-01-01', null union
	select	'2005-01-02', null union
	select	'2005-01-03', null

select 	t2.thedate,
	t1.revenues
from (	select 	count(*) row,
       		a.revenues
	from @table_1 a 
	inner join @table_1 b
		on a.revenues >= b.revenues
	group by a.revenues) t1 
join (	select 	count(*) row,
       		a.thedate
	from @table_2 a 
	inner join @table_2 b
		on a.thedate >= b.thedate
	group by a.thedate) t2
on t1.row = t2.row

Nathan Skerl

dirwin26
Yak Posting Veteran

81 Posts

Posted - 2005-10-04 : 08:36:08

I will have to create identifiers. As of now, there are none, but I know that the data matches exactly, so I would be safe in placing them side by each.

thanks!

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-10-04 : 10:21:38

Keep in mind that the rows are stored in no particular order within the database. Your query can change from time to time based on many conditions (optimizer, indexing, etc).

Just because they match side by side in Query Analyzer doesnt mean squat.

Nathan Skerl

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources