| Author |
 Topic  |
|
|
Kimi86
Yak Posting Veteran
54 Posts |
 Posted - 05/24/2012 : 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
USA
15566 Posts |
Posted - 05/24/2012 : 13:00:43
|
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 Note that you can't include temp tables in a view definition, so I changed them to regular table references. |
 |
|
|
HenryFulmer
Yak Posting Veteran
USA
98 Posts |
Posted - 05/24/2012 : 13:10:24
|
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
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48026 Posts |
Posted - 05/24/2012 : 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
Yak Posting Veteran
USA
98 Posts |
Posted - 05/25/2012 : 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
India
48026 Posts |
Posted - 05/25/2012 : 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/
|
|
|
| |
Topic |
|
|
|
Need help with a query..
