MySQL Query giving error

Question

I am trying to get the flights no whose seats are more 0. I have 3 table in MySQL

1. Sector
2. Flights
3. Aircraft

please see the image for structure of table in sequence of 1,2 and 3 enter image description here

I am writting this SQL

select * from aircraft 
where aircrafttypeID=
       (select aircrafttypeID 
            from sector,flights 
             where source like 'Kolkata' 
             and destination like 'Ahmedabad' 
             and sector.sectorID=flights.sectorID) 
and bseats>0

This query is giving error -

Subquery returns more than 1 row

because subquery is retuning multiple flight numbers. So I need some help how can get those Flights Number whose seats are more than 0

@Pheonix answer is a better fix for you in my view. The join is better and cleaner than the subquery to use in this case. — jiraiya
– jiraiya, Commented Jun 14, 2013 at 8:32

Prashant16 · Accepted Answer · 2013-06-14 08:12:00Z

3

Try this

SELECT  *
FROM    aircraft
WHERE   aircrafttypeID IN ( SELECT   aircrafttypeID
                            FROM     sector ,
                                     flights
                            WHERE    source LIKE 'Kolkata'
                                     AND destination LIKE 'Ahmedabad'
                                     AND sector.sectorID = flights.sectorID
                     )
    AND bseats > 0

answered Jun 14, 2013 at 8:12

Prashant16

1,5264 gold badges18 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ankur Trapasiya · Accepted Answer · 2013-06-14 08:49:43Z

2

Just a suggestion. You can use join instead on subquery.

select * 
from aircraft a 
inner join flights f
on a.aircrafttypeID=f.aircrafttypeID
inner join source s 
on s.sectorID=f.sectorID
and s.source LIKE 'Kolkata'
and s.destination LIKE 'Ahmedabad'
and a.bseats>0

edited Jun 14, 2013 at 8:49

answered Jun 14, 2013 at 8:17

Ankur Trapasiya

2,2004 gold badges22 silver badges43 bronze badges

Collectives™ on Stack Overflow

MySQL Query giving error

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related