Click here to Skip to main content
Rate this: bad
good
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
1---------------1
7---------------2
3---------------3
5---------------4
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 8:43am
Ger Hayden24.6K

1 solution

Rate this: bad
good
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 | :)
  Permalink  
v2
Comments
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 Sergey Alexandrovich Kryukov 383
1 OriginalGriff 304
2 George Jonsson 258
3 Animesh Datta 130
4 Shemeemsha RA 128
0 OriginalGriff 6,099
1 Sergey Alexandrovich Kryukov 5,411
2 CPallini 4,770
3 George Jonsson 3,400
4 Gihan Liyanage 2,522


Advertise | Privacy | Mobile
Web04 | 2.8.140916.1 | Last Updated 13 Jun 2011
Copyright © CodeProject, 1999-2014
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