MySql Joining two tables

Question

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

1. PNR=> Auto Increment. Primary. Int value

2. Train Number => Integer. Unique

3. Train Name => Varchar. Unique

4. Name of the passenger=> Varchar

5. Age => Int Value

6. date => date of Journey

7. Seating => Varchar

8. src => varchar

9. des => Varchar

10. class => varchar (ACI,ACII,ACIII,SL)

Table res and their attributes:

1. id=> Auto Increment.Primary Key

2. Train Number => Integer

3. Train Name => Varchar

4. src => Varchar

5. des => Varchar

6. date => date of Journey

7. AC_I => No of Tickets.Integer value

8. First_AC => Fare

9. AC_II => No of Tickets.Integer value

10. SEC_AC => Fare

11. AC_III => No of Tickets.Integer value

12. THIRD_Ac => Fare

13. SL=> No of Tickets for Sleeper Class.Integer value

14. 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?

Answer 1

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 ;