Click here to Skip to main content
11,480,532 members (57,970 online)
Rate this: bad
Please Sign up or sign in to vote.
See more: SQL MySQL
Route 1 is defined as with the following stages on my RouteStage table
Stage ID SequenceNumber
Passenger X travels from along Route 1 from Stage 1 to Stage 3
Then my mythical user decides to delete Stage 7
But I dont want to allow it because the fare is composed of :
Stage 1: Boarding Tarriff
Stage 7: Transit Tarrif
Stage 3: Jumping off Tarrif
PassengerHistory holds PassengerID, RouteID, StartStage and EndStage.
I am trying to but together a query to join PassengerHistory with RouteStage in a way that I can detect that stage 7 has been used
MySQL helpfully tells my that I have Error 1064 - error in SQL syntax
This is what my effort looks like:
select t1.route_ID, t1.stage_id, t1.end_stage_id from passengerhistory t1, routestage t2
where t1.route_id = t2.route_id
and t2.stage_id = 7;
and t2.sequenceNumber >= (select sequencenumber from routestage
where route_id = t2.route_id
and stage_id = t2.stage_id);
I want to force my user to put an end date on this routestage record and define a new one to commence the following day without stage 7 so that
all tickets already issued incorporating stage 7 do not loose that information.
Posted 13-Jun-11 9:43am
Ger Hayden27.9K

1 solution

Rate this: bad
Please Sign up or sign in to vote.

Solution 1

I suspect that the error is that the embedded select may return multiple rows. Usually I will have an embedded select looking like this
 and t2.sequenceNumber = ( select min(sequencenumber) from routestage
Not sure if that is going to help you though. Smile | :)
Ger Hayden at 14-Jun-11 0:24am
Hi Chris,
That should not happen in practice, any given stage can only occur once on a route and so will have a unique sequence number. The sequencenumber is introduced to allow flexibility in reorganizing routes. If this were a real example, Stages 1 and 3 would have been shortened where they meet with the gab becomming a new stage - the next ID being 7, which it why the sequence in 1, 7, 3. (Stage 2 is long moved to a differant route).
Ger Hayden at 14-Jun-11 3:07am
Apologies Chris and anyone else investigating this one - it belongs in the hall of shame - theres a rogue ; at the end of the third line.

Chris - I am accepting your solution to 'Close' the question.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 215
1 Richard MacCutchan 150
2 CHill60 90
3 CPallini 70
4 Dave Kreskowiak 70
0 Sergey Alexandrovich Kryukov 7,874
1 OriginalGriff 7,556
2 Sascha Lefèvre 3,074
3 Maciej Los 2,491
4 Richard Deeming 2,335

Advertise | Privacy | Mobile
Web04 | 2.8.150520.1 | Last Updated 13 Jun 2011
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100