I am running into an issue while trying to join two MySQL tables and getting output.
My first table named res
having 14 columns related to trains bookings. It has all the information of all trains with their date of Journey, available tickets, and their fares.
Table definition for rest
.
CREATE TABLE `abcd`.`rest` (
`PNR` INT NOT NULL AUTO_INCREMENT ,
`Tr_Num` INT( 6 ) NULL ,
`Train_Name` VARCHAR( 60 ) NULL ,
`na` VARCHAR( 60 ) NULL ,
`age` INT( 3 ) NULL ,
`DOJ` DATE NULL ,
`seat` VARCHAR( 20 ) NULL ,
`src` VARCHAR( 60 ) NULL ,
`des` VARCHAR( 60 ) NULL ,
`class` VARCHAR( 60 ) NULL ,
PRIMARY KEY ( `PNR` )
) ENGINE = INNODB;
Table rest
This table has 10 columns
-
PNR=> Auto Increment. Primary. Int value
-
Train Number => Integer. Unique
-
Train Name => Varchar. Unique
-
Name of the passenger=> Varchar
-
Age => Int Value
-
date => date of Journey
-
Seating => Varchar
-
src => varchar
-
des => Varchar
-
class => varchar (ACI,ACII,ACIII,SL)
Table res
and their attributes:
-
id=> Auto Increment.Primary Key
-
Train Number => Integer
-
Train Name => Varchar
-
src => Varchar
-
des => Varchar
-
date => date of Journey
-
AC_I => No of Tickets.Integer value
-
First_AC => Fare
-
AC_II => No of Tickets.Integer value
-
SEC_AC => Fare
-
AC_III => No of Tickets.Integer value
-
THIRD_Ac => Fare
-
SL=> No of Tickets for Sleeper Class.Integer value
-
SL_CLASS => Fare
The SQL command that I have used and was able to get something out of it but was not completely successful.
select rest.PNR, res.TRAIN_NUMBER,res.Tr_name,rest.na,rest.age, rest.doj,
rest.seat, rest.src, rest.des,rest.class, case when rest.class = 'AC I'then res.AC_I
when rest.class = 'AC II' then res.AC_II
when rest.class = 'AC III' then res.AC_III
when rest.class = 'Sleeper Class' then res.SL_CLASS
end as Tickets
from rest, res where rest.DOJ = res.date order by PNR desc limit 2;
After running this command I get the output as below.
+-------+----------+----------+------+------+----------+------+------+------+------+--------+
| PNR | TRAIN_NR | Tr_name | Name | Age | doj | seat | src | des |class | Tickets|
+-------+----------+----------+------+------+----------+------+------+------+------+--------+
| 40 | 10008 | Mum | S | 42 |2018-04-27| LB | M | H | AC I | 480 |
+-------+----------+----------+------+------+----------+------+------+------+------+--------+
Now, What I am trying to do is along with this output I want to know fetch the Fare of the ticket.
I am expecting an output something like this.
+-------+----------+----------+------+------+----------+------+------+------+------+--------+--------+
| PNR | TRAIN_NR | Tr_name | Name | Age | doj | seat | src | des |class | Tickets| fare |
+-------+----------+----------+------+------+----------+------+------+------+------+--------+--------+
| 40 | 10008 | Mum | S | 42 |2018-04-27| LB | M | H | AC I | 480 | ??? |
+-------+----------+----------+------+------+----------+------+------+------+------+--------+--------+
How can I achieve this?
Best Answer