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.