MySql Joining two tables 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?
select rest.PNR, res.TRAIN_NUMBER, res.Tr_name, res.AC_I, rest.na,rest.age
     , rest.doj, rest.seat, rest.src, rest.des, rest.class
     , case when rest.class = 'AC I' then res.FIRST_AC
            when rest.class = 'AC II' then res.SEC_AC 
            when rest.class = 'AC III' then res.THIRD_AC 
            when rest.class = 'Sleeper Class' then res.SL_CLASS 
       end as fare
     , 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 
       end as tickets
from res, rest 
where rest.Tr_Num = res.TRAIN_NUMBER 
order by PNR desc limit 4 ;