user3237917 user3237917 - 2 months ago 7
MySQL Question

Mysql syntax help please sql

I have made a railway database with fare in it now I want to retrieve value or cost of like destination a to b - how? Please help me retrieve and how to make the tables I want it for my project? I don't want codes only help to make it.

Answer

Since I don't see your sample data, but based on your comment alone, I would do something like...

select
      r.cost
   from
      YourRailFairTable r
   where
         (    r.fro = 'from destination'
          AND r.t = 'to destination' )
      OR (    r.t = 'from destination'
          AND r.fro = 'to destination' )

Since I don't know how your table is organized, I have an OR in the where clause. Because someone could go From "City A" to "City B" or From "City B" to "City A". Therefore, we can never assume that it will always be in a specific from/to order unless you somehow forced the data to have the destinations in alpha order. This way it gets in EITHER case. Just make sure you have an index on your table for both (fro, t)

FEEDBACK PER COMMENT

Not dealing with such a system of from/to type of ticketing system, I would do the following. Create one table of all possible destinations and have an auto-increment ID column. Have another table of all routes and rates. The bigger problem that really requires more effort is something I can not directly answer... Such as in air travel, a person might want to go from city A to city B, but the airline has no direct flight and needs to go from A to city X to city B. You have nothing that links them together so you would need additional logic to handle that. But for rail travel it may not be that complex, even if there are switching trains at certain stations.

CREATE TABLE Destination (
   DestinationID INT NOT NULL AUTO_INCREMENT,
   Destination CHAR(30) NOT NULL,
   PRIMARY KEY (DestinationID),
   INDEX Destination (Destination) );

then your values would be something like

DestinationID  Destination
1              City A
2              City B
3              City ...Z

Next, your rates table which has an ID to both from/to destination and the rate. In this case, any insertions I would FORCE the first destination to the lower "ID" value, so even if a destination name is spelled incorrectly and adjusted, the internal ID wont.

CREATE TABLE RailRates (
   RailRateID INT NOT NULL AUTO_INCREMENT,
   DestFrom INT,
   DestTo   INT,
   Rate     DECIMAL(7,2),
   PRIMARY KEY (RailRateID),
   FOREIGN KEY (DestFrom) REFERENCES Destination ( DestinationID )
      ON DELETE CASCADE,
   FOREIGN KEY (DestTo) REFERENCES Destination ( DestinationID )
      ON DELETE CASCADE,
   INDEX FromTo( DestFrom, DestTo) );

Sample Data for rates table

RailRateID  DestFrom  DestTo   Rate
1           1         2        123.45
2           1         3        145.67
3           1         9        287.42
4           1         14       321.93
5           2         3        46.82
6           2         9        187.18
7           etc...

Then, you would prompt a user for from/to locations, get their IDs and put them in low/high order as it would not matter which from/to and update the query something like

select
      r.Rate
   from
      RailRates r
   where
          r.FromDest = lowIDNumberOfOneLocation
      AND r.ToDest = highIDNumberOfOtherLocation