Need help with a query.. - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Need help with a query..

Author

Topic

Kimi86
Yak Posting Veteran

79 Posts

Posted - 2012-05-24 : 12:52:31

Hi All,

I have two table with same kind of data but different keys. One is History and one is active.I need to merge the rerults in a view joing them with a thrird Taable.

Below are scripts for a simplified example

Scripts:
-------
Create Table #T1
(
Id int,
Descp varchar(10)
)

Insert into #T1
Select 1,'One'
Union
Select 2,'Two'
Union
Select 3,'Three'
Union
Select 4,'Four'
Union
Select 5,'Five'

Select Id,id + 1000 Ranks into #T2 from #T1 where ID in (1,2,3)
Select Id,id + 1000 Ranks into #T3 from #T1 where ID in (4,5)

Select * from #T1
Select * from #T2
Select * from #T3

Expected Output:
-----------------

id Descp Ranks
1 One 1001
2 Two 1002
3 Three 1003
4 Four 1004
5 Five 1005

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-05-24 : 13:00:43

[code]CREATE VIEW myView AS
SELECT A.ID, A.Descp, B.Ranks FROM T1
INNER JOIN T2 ON T1.ID=T2.ID
UNION ALL
SELECT A.ID, A.Descp, B.Ranks FROM T1
INNER JOIN T3 ON T1.ID=T3.ID[/code]Note that you can't include temp tables in a view definition, so I changed them to regular table references.

HenryFulmer
Posting Yak Master

110 Posts

Posted - 2012-05-24 : 13:10:24

[code]
select #t1.*, a.Ranks from #t1
inner join
(select id, Ranks from #t2 union all select id, Ranks from #t3) a
on #t1.id= a.id
[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-24 : 15:56:48

quote:

Originally posted by HenryFulmer


select #t1.*, a.Ranks from #t1 
inner join 
(select id, Ranks from #t2 union all select id, Ranks from #t3) a
on #t1.id= a.id

sorry didnt understand how this will work

As i see OP is populating #T1 and #t2 with different sets of id so i dont think join on id will return anything

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

HenryFulmer
Posting Yak Master

110 Posts

Posted - 2012-05-25 : 15:10:21

quote:
Originally posted by visakh16

quote:
Originally posted by HenryFulmer

select #t1.*, a.Ranks from #t1
inner join
(select id, Ranks from #t2 union all select id, Ranks from #t3) a
on #t1.id= a.id

sorry didnt understand how this will work

As i see OP is populating #T1 and #t2 with different sets of id so i dont think join on id will return anything

----------------------------------------------------------------------

Using the example from the original post this is the result set that is being returned:

id Descp Ranks
1 One 1001
2 Two 1002
3 Three 1003
4 Four 1004
5 Five 1005

Here's the script I used:


Create Table #T1
(
Id int,
Descp varchar(10)
)

Insert into #T1 
Select 1,'One'
Union
Select 2,'Two'
Union
Select 3,'Three'
Union
Select 4,'Four'
Union
Select 5,'Five'

Select Id,id + 1000 Ranks into #T2 from #T1 where ID in (1,2,3)
Select Id ,id + 1000 Ranks into #T3 from #T1 where ID in (4,5)

select #t1.*, a.Ranks from #t1 inner join 
(select id, Ranks from #t2 union all select id, Ranks from #t3) a
on #t1.id= a.id

Is my logic flawed?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-25 : 15:32:36

oh ok...you're doing union all inside
sorry didnt notice that
looks fine now

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources