How to join temp tables which have no relation to

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 2008 Forums

Transact-SQL (2008)

How to join temp tables which have no relation to

Author

Topic

Sql_forum
Yak Posting Veteran

50 Posts

Posted - 2011-11-09 : 05:39:38

Hi I want to Join multiple temp tables which don't have any relation each other. Due to that i can't able to write 'ON' condition.

I have tried to add a a unique column to all temp tables to join them based on that Unique column, however if any one of these temp tables have no rows then my unique is not inserted into temp table

and the final join result also returns no rows

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-09 : 06:16:00

Try a left join

select *
from #tmp1 t1
left join #tmp2 on t2.id = t1.id
left join #tmp3 on t3.id = t1.id

If there's no biggest table

select *
from (select id frmo #tmp1 union select id from #tmp2 union select id from #tmp3) t
left join #tmp1 on t1.id = t.id
left join #tmp2 on t2.id = t.id
left join #tmp3 on t3.id = t.id

But you probably should think about why you want to do this.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-09 : 06:26:26

quote:
Originally posted by Sql_forum

Hi I want to Join multiple temp tables which don't have any relation each other. Due to that i can't able to write 'ON' condition.

I have tried to add a a unique column to all temp tables to join them based on that Unique column, however if any one of these temp tables have no rows then my unique is not inserted into temp table

and the final join result also returns no rows

if they have no relationship with each other what will be use in adding unique id and relating them with that?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-11-09 : 06:29:29

show us your table, sample data and the exprected result

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources