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
 General SQL Server Forums
 New to SQL Server Programming
 JOIN help

Author  Topic 

Ranjit.ileni
Posting Yak Master

183 Posts

Posted - 2011-03-29 : 02:53:26
Hi,

Here is table DDL

create table #temp
(
flightname varchar(20)
,segmentname varchar(20)
,depatureplace varchar(20)
,arrivalplace varchar(20)
)

insert into #temp
select 'KL123','kl123a','aaaa','cccc'union all
select 'KL123','kl123b','cccc','dddd'union all
select 'KL123','kl123c','dddd','bbbb'union all
select 'KL126','kl126a','dddd','bbbb'union all
select 'KL126','kl126b','bbbb','cccc'union all
select 'KL128','kl128','aaaa','dddd'

select * from #temp
flightname segmentname depatureplace arrivalplace
KL123 kl123a aaaa cccc
KL123 kl123b cccc dddd
KL123 kl123c dddd bbbb
KL126 kl126a dddd bbbb
KL126 kl126b bbbb cccc
KL128 kl128 aaaa dddd

Here

KL123 flight has 3 segments(aaaa-cccc ,cccc-dddd,dddd-bbbb)
KL126 flight has 2 segments(dddd-bbbb ,bbbb-cccc)
KL128 has only one segment(aaaa-dddd)

segment means Break journeys

Expected output:

flightname depatureplace arrivalplace
KL123 aaaa bbbb
kl126 dddd cccc
KL128 aaaa dddd

Please help me how to form a Query

--Ranjit

Devart
Posting Yak Master

102 Posts

Posted - 2011-03-29 : 03:20:56
Hello.

Try this:

SELECT
flightname,
depatureplace,
(SELECT TOP 1 arrivalplace FROM #temp WHERE flightname=t.flightname ORDER BY segmentname DESC) AS arrivalplace
FROM
(
SELECT
row_number()OVER (PARTITION BY flightname ORDER BY segmentname) AS rn,
flightname,depatureplace
FROM
#temp
) t
WHERE
rn=1

Devart,
SQL Server Tools:
dbForge Data Studio
dbForge Schema Compare
dbForge Data Compare
dbForge SQL Complete
Go to Top of Page

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2011-03-30 : 18:16:07
[code]
select frst.flightName, frst.departurePlace, lst.arrivalPlace
from #temp frst --this is first/starting segment
join #temp lst on frst.flightName = lst.flightName --this is last/ending segment
where --there is no previous segment for the first one
not exists(select *
from #temp prv
where frst.flightName = prv.flightName
and prv.arrivalPlace = frst.departurePlace)
--there is no next segment for the last one
and not exists(select *
from #temp nxt
where lst.flightName = nxt.flightName
and lst.departurePlace = nxt.arivalPlace)[/code]

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/
Go to Top of Page

Ranjit.ileni
Posting Yak Master

183 Posts

Posted - 2011-03-31 : 02:14:05
Hi mmarovic,

your Query Results not matching with my Expected Output.

KL123 aaaa cccc --bbbb
KL126 dddd bbbb --cccc
KL128 aaaa dddd



--Ranjit
Go to Top of Page

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2011-03-31 : 02:42:37
quote:
Originally posted by Ranjit.ileni

Hi mmarovic,

your Query Results not matching with my Expected Output.

KL123 aaaa cccc --bbbb
KL126 dddd bbbb --cccc
KL128 aaaa dddd



--Ranjit


My bad, there was a mistake in the query. You should be able to fix it yourself if you understood comments:
select frst.flightName, frst.departurePlace, lst.arrivalPlace
from #temp frst --this is first/starting segment
join #temp lst on frst.flightName = lst.flightName --this is last/ending segment
where --there is no previous segment for the first one
not exists(select *
from #temp prv
where frst.flightName = prv.flightName
and prv.arrivalPlace = frst.departurePlace)
--there is no next segment for the last one
and not exists(select *
from #temp nxt
where lst.flightName = nxt.flightName
and nxt.departurePlace = lst.arivalPlace)


Mirko

My blog: http://mirko-marovic-eng.blogspot.com/
Go to Top of Page

Ranjit.ileni
Posting Yak Master

183 Posts

Posted - 2011-03-31 : 02:50:05
I fix the problem by changing join condition

thank you very much.

--Ranjit
Go to Top of Page
   

- Advertisement -