Reputation: 2700
select 1 as id,
'A' as place
into #places
UNION
select 2 as id,
'B' as place
UNION
select 3 as id,
'C' as place
UNION
select 4 as id,
'D' as place
select 'x' as name,
2 as start,
3 as endd
into #travel
UNION
select 'y' as name,
4 as start,
1 as endd
UNION
select 'z' as name,
1 as start,
3 as endd
select * from #places
select * from #travel
Now I want to get name/start place/end place using joins?
Upvotes: 0
Views: 64
Reputation: 2760
select #travel.*, pstart.place as start, pend.place as end
from #travel inner join #places as pstart on pstart.id = #travel.start
inner join #places as pend on pend.id = #travel.end
Upvotes: 0
Reputation: 27132
SELECT
t.name,
p1.place as start_place,
p2.place as end_place
FROM
travel t
JOIN places p1 on t.start = p1.id
JOIN places p2 on t.endd = p2.id
Upvotes: 1