Row with first/last from other tables

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)

Row with first/last from other tables

Author

Topic

simmer
Starting Member

5 Posts

Posted - 2011-05-23 : 09:25:29

Table 1 is a Schedule (in this case, a train). A grid view shows all the schedules, their effective dates, running number, etc, and - this is the difficult bit - their origin and destination locations. In the case of schedules which have been created but no location information entered, these fields will be blank.

e.g.
ScheduleID - Name - Origin - Destination
1 - 1A34 - London - Glasgow
2 - 3B45 - Glasgow - London
3 - 5B65 - [null] - [null]

Table 2 is Location which has an ID and a textual name. This is the text I want displayed in the grid.

LocationID - Name
1 - London
2 - Glasgow

Table 3 is ScheduleLocation which has the Schedule ID, the Location ID, and a sequence (a simple incrementing number, so the lowest numbered sequence is the origin and the highest sequence number is the destination).

ScheduleID - LocationID - Sequence - Time
1 - 1 - 1 - 10:00
1 - 2 - 54 - 14:00
2 - 1 - 30 - 14:10
2 - 2 - 5 - 10:15

So, in the query that returns the Schedule data, it needs to pull the lowest Sequence record for that schedule and look up the appropriate location name, then do the same for the highest sequence.

Sorry, no CREATE statements as I've been lazy and used MS SQL 2008 table designer and I haven't worked out how to export table structures yet.

Any help appreciated! TIA

simmer
Starting Member

5 Posts

Posted - 2011-05-23 : 09:28:14

To clarify, Origin and Destination in the first table are not actual columns in the database, but columns in the query to calculate that information.

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2011-05-23 : 10:42:12

I'm not sure if this is exactly what you're looking for. But this will get the highest and lowest sequence number and use them for orign and destination based lowest and highest recpectively. I added a mid sequence for schedule 1 which will get ignored as it is neither the highets or lowest.

IF OBJECT_ID('Schedule') > 0 BEGIN DROP TABLE Schedule END;
IF OBJECT_ID('Location') > 0 BEGIN DROP TABLE Location END;
IF OBJECT_ID('ScheduleLocation') > 0 BEGIN DROP TABLE ScheduleLocation END;

Create table Schedule
(
ScheduleID int ,
Name varchar(50)
)

Create table Location
(
LocationID int ,
Name varchar(50)
)

Create table ScheduleLocation
(
ScheduleID int,
LocationID int,
Sequence int,
ScheduleTime time(0)
)

insert into Schedule values(1,'1A34'),(2,'3B45') ,(3, '5B65')
insert into Location values(1,'London'),(2,'Glasgow')
insert into ScheduleLocation values(1 , 1 , 1 , '10:00'),(1 , 2 , 54 , '14:00'),(2 , 1 , 30 , '14:10'),(2 , 2 , 5 , '10:15'),(1 , 2 , 39 , '15:00')

IF OBJECT_ID('Schedule') > 0 BEGIN DROP TABLE Schedule END;
IF OBJECT_ID('Location') > 0 BEGIN DROP TABLE Location END;
IF OBJECT_ID('ScheduleLocation') > 0 BEGIN DROP TABLE ScheduleLocation END;

;With MySchedule
as
(
Select scheduleid, MIN(Sequence) Origin,Max(Sequence)Destination from ScheduleLocation group by scheduleid
)
,MyResults
as
(
Select S.Scheduleid,O.LocationID OriginLocationID,O.ScheduleTime departureTime,D.LocationID DestinationLocationID,D.ScheduleTime ArrivalTime from MySchedule S
inner join ScheduleLocation O
on S.scheduleid = O.scheduleid and S.Origin = O.Sequence
inner join ScheduleLocation D
on S.scheduleid = D.scheduleid and S.Destination = D.Sequence

)

Select Scheduleid,O.name Departing,DepartureTime,D.name Arriving, ArrivalTime
from MyResults R
inner join Location D
on DestinationLocationID = D.locationID
inner join Location O
on OriginLocationID = O.locationID

simmer
Starting Member

5 Posts

Posted - 2011-05-23 : 11:08:53

Wow, that looks exactly what I'm looking for! No wonder I wasn't able to write a query like that. I now need to sit down and understand what it's doing exactly, and how to work it into the gridview.

Many thanks.

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2011-05-23 : 11:15:29

You might want to put it in a view or a stored proc and then just select * from the view or execute the proc.

best of luck.

simmer
Starting Member

5 Posts

Posted - 2011-05-23 : 11:23:04

Hmm, where do I put the additional columns in the Schedule table? For example, the resulting columns are ScheduleID, Departing, departureTime, Arriving, and arrivalTime. However, I also want Name (and others) from the Schedule table. In the example that would be the 1A34, 3B45, and 5B65 values.

I'm guessing I need another outer query, unless there's a simpler way?

It's surprisingly fast despite having:
Schedules: 303694
Schedule Locations: 5321715
Locations: 10213

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2011-05-23 : 19:09:40

You can add the schedule table to the SQL statment that uses the CTE or adjust the MyResults CTE to conatin additional columns. I added it to the SQL Statment. In your ammedment to your description of the problem, you stated that Origin and Destination weren't actually column in the schedule table - so I figured only ScheduleID and Name were relevant to the solution.

;With MySchedule
as
(
Select scheduleid, MIN(Sequence) Origin,Max(Sequence)Destination from ScheduleLocation group by scheduleid
)
,MyResults
as
(
Select S.Scheduleid,O.LocationID OriginLocationID,O.ScheduleTime departureTime,D.LocationID DestinationLocationID,D.ScheduleTime ArrivalTime from MySchedule S
inner join ScheduleLocation O
on S.scheduleid = O.scheduleid and S.Origin = O.Sequence
inner join ScheduleLocation D
on S.scheduleid = D.scheduleid and S.Destination = D.Sequence

)

Select r.Scheduleid,SC.name schedule,O.name Departing,DepartureTime,D.name Arriving, ArrivalTime
from MyResults R
inner join Location D
on DestinationLocationID = D.locationID
inner join Location O
on OriginLocationID = O.locationID
Inner Join Schedule SC
on SC.ScheduleID = R.Scheduleid

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources