0

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?

  • 1
    Please edit your post with CREATE TABLE statements and INSERT statements for sample data to reproduce the problem. Alternatively provide a link to db<>fiddle or similar – Lennart May 24 '18 at 13:23
  • Lennart. I have created the table along with my DB from Php Myadmin. So, you can understand I have not created any DB/Tables with commands. – Koushik.S May 24 '18 at 13:36
  • @Koushik.S - what Lennart meant was your post. Its easier to read if your table definition is like a "create table" script of sql and not like that – Jack Frost May 24 '18 at 13:46
  • You can get the create table using SHOW CREATE TABLE rest ; – ypercubeᵀᴹ May 25 '18 at 9:38
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 ;
  • Is that meant to be an answer or an edit to your question? – ypercubeᵀᴹ May 24 '18 at 19:39
  • That's an answer. – Koushik.S May 25 '18 at 7:22
  • Yeah, CASE... is the secret sauce. – Rick James Jun 2 '18 at 21:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.