Sub query return more than one field

Question

I have a table structure like this Table Structure

Here I want to select all the data from bus_routines table with bus details and avaliable_seat which is calculated from buses.number_of_seat - reserved_seats.number_of_reserved_seat - booking.number_of_seat even if the data is not present in booking table and reserved_seats table too where the bus_routines.sector_from=Ktm ,bus_routines.sector_to=Pkr and bus_routines.date=2015-12-15

Relation between them are :

buses and bus_routines --> one to many
bus_routines and booking --> one to many
bus_routines and reserved_seats --> one to many

I have tried the following query

SELECT r.* , b.* ,
    (
    SELECT b.number_of_seat - sum(booking.number_of_seat)-sum(reserved_seats.number_of_reserved_seat) 
    FROM bus_routines AS r 
    INNER JOIN buses AS b 
        ON b.id = r.bus_id 
    INNER JOIN 
        (SELECT number_of_seat , bus_routine_id FROM booking GROUP BY  booking.bus_routine_id) AS booking 
        ON booking.bus_routine_id = r.id 
    INNER JOIN (SELECT number_of_reserved_seat , routine_id FROM reserved_seats GROUP BY reserved_seats.routine_id) AS reserved_seats 
    ON r.id = reserved_seats.routine_id
    WHERE 

    r.sector_from = "KTM" AND 
    r.sector_to = "PKR" AND 
    r.departure_date = "2015-12-15" 
    ) AS avaliable_seat 
FROM bus_routines AS r 
INNER JOIN buses AS b 
    ON b.id = r.bus_id 
WHERE 

    r.sector_from = "KTM" AND 
    r.sector_to = "PKR" AND 
    r.departure_date = "2015-12-15" 
HAVING avaliable_seat > 0

I get the result what I want but the avaliable_seat is same for all the row

I have tried another query too but it give me the single result

SELECT r.* , b.* , b.number_of_seat - sum(booking.number_of_seat)-sum(reserved_seats.number_of_reserved_seat) AS available_seat

FROM bus_routines AS r 
INNER JOIN buses AS b 
    ON b.id = r.bus_id 
INNER JOIN 
    (SELECT number_of_seat , bus_routine_id FROM booking GROUP BY booking.bus_routine_id) AS booking 
    ON booking.bus_routine_id = r.id 
INNER JOIN 
    (SELECT number_of_reserved_seat , routine_id FROM reserved_seats GROUP BY reserved_seats.routine_id) AS reserved_seats 
    ON r.id = reserved_seats.routine_id
WHERE   
    r.sector_from = "KTM" AND 
    r.sector_to = "PKR" AND 
    r.departure_date = "2015-12-15" 
HAVING available_seat > 0

I also tried another query and it give me Subquery returns more than 1 row . The query is

SELECT r.* , b.* ,
b.number_of_seat - (SELECT sum(number_of_seat) FROM booking GROUP BY  booking.bus_routine_id)
 - (SELECT sum(number_of_reserved_seat) FROM reserved_seats GROUP BY reserved_seats.routine_id) AS available_seat

FROM bus_routines AS r 
INNER JOIN buses AS b 
    ON b.id = r.bus_id 
WHERE 
    r.sector_from = "KTM" AND 
    r.sector_to = "PKR" AND 
    r.departure_date = "2015-12-15" 
HAVING available_seat > 0

your last query gives me a result with 3 rows....i dont see any `subquery returned more than one row` error — akhil kumar, Jan 04 '16 at 07:00
Yes, in sqlfiddle return the 3 rows but when I try it in localhost it return the error `subquery returned more than one row` . But , in sqlfiddle it doesn't return the correct result too — Kiran Subedi, Jan 04 '16 at 07:13

score 0 · Accepted Answer · answered Jan 08 '16 at 15:41

One approach is to use correlated subqueries to get the reserved_seats and booked_seats for each bus_routine.

Let's assume that this query returns the rows you are wanting to return, it's just missing the available_seat column you want calculated:

SELECT r.*
     , b.*
  FROM bus_routines r
  JOIN buses b 
    ON b.id = r.bus_id 
 WHERE r.sector_from    = 'KTM' 
   AND r.sector_to      = 'PKR'
   AND r.departure_date = '2015-12-15'

To number of "reserved seats" for a given bus_routine, you can query the reserved_seats table, like this:

SELECT IFNULL(SUM(s.number_of_reserved_seat),0)
  FROM reserved_seats s
 WHERE s.routine_id = '649'

And the number of "booked seats" for a given bus_routine can be returned from the booking table, like this:

SELECT IFNULL(SUM(k.number_of_seat),0)
  FROM booking k
 WHERE k.bus_routine_id = '649'

We can incorporate the queries to get "reserved seats" and "booked seats" into the first query, as correlated subqueries. In place of the literal '649', with a reference the id from the bus_routine table.

SELECT r.*
     , b.*

       -- number of booked seats
     , ( SELECT IFNULL(SUM(k.number_of_seat),0)
           FROM booking k
          WHERE k.bus_routine_id = r.id
       ) AS booked_seats

       -- number of reserved seats
     , ( SELECT IFNULL(SUM(s.number_of_reserved_seat),0)
           FROM reserved_seats s
          WHERE s.routine_id = r.id 
       ) AS reserved_seats

       -- calculate available seats as
       -- (bus number_of_seat) - (booked_seats) - (reserved_seats) 
     , (  b.number_of_seat 
       - ( SELECT IFNULL(SUM(k.number_of_seat),0)
             FROM booking k
            WHERE k.bus_routine_id = r.id
         ) 
       - ( SELECT IFNULL(SUM(s.number_of_reserved_seat),0)
             FROM reserved_seats s
            WHERE s.routine_id = r.id 
         )
       ) AS avaliable_seat

  FROM bus_routines r
  JOIN buses b 
    ON b.id = r.bus_id 
 WHERE r.sector_from    = 'KTM' 
   AND r.sector_to      = 'PKR'
   AND r.departure_date = '2015-12-15'

If there's no requirement to return the booked_seats and reserved_seats columns, those can be omitted from the query. The subqueries to get those values can just appear in the calculation of the available_seat column.

SQL Fiddle demonstration here: http://sqlfiddle.com/#!9/64eaa/7

score -1 · Answer 2 · edited May 23 '17 at 12:03

-1

Please try GROUP_CONCAT for return one column from multiple in subquery. refer the links.

How to use GROUP_CONCAT in a CONCAT in MySQL

http://www.w3resource.com/mysql/aggregate-functions-and-grouping/aggregate-functions-and-grouping-group_concat.php

edited May 23 '17 at 12:03

Community

1
1

answered Jan 04 '16 at 07:00

Jalpa

697
3
13

Sub query return more than one field

2 Answers2