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?
CREATE TABLE
statements andINSERT
statements for sample data to reproduce the problem. Alternatively provide a link to db<>fiddle or similarSHOW CREATE TABLE rest ;