CODEWITHSUNDEEP
sqlsql-serversubquery
Intekhab Khan
Intekhab Khan

Reputation: 1863

How to get single result in from sql select query! SQL

database diagram

above are the part of my database diagram where i am getting problem!

select columns from 3 table with the help of referencing from 1 outside table base_table and 3 table . But Sql displaying a error 

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >

How to resolve this! Is there is any better way of doing this. I am new to sql and this is the part of my school project

One more thing the base_id from base_table i i will pass through the procedure in C# The following are the code which giving the error. 

USE [shangrila]
select 
a.source,a.destination,
b.flight_name,
c.flight_cost,c.flight_seat,c.flight_type,
d.flight_time
from
base_table a,
flight_main_table b,
flight_cost_table c,
flight_timing_table d
where
a.base_id=109 and
b.base_id=109 and
c.flight_id=(select flight_id from flight_main_table where base_id=109) and
d.flight_id=(select flight_id from flight_main_table where base_id=109) 
go

Upvotes: 1

Views: 5556

Answers (7)

Joe Stefanelli
Joe Stefanelli

Reputation: 135808

I've cleaned up the query a bit, using explicit joins and some more meaningful table aliases. Let's try this:

SELECT bt.source, bt.destination,
       fmt.flight_name,
       fct.flight_cost, fct.flight_seat, fct.flight_type,
       ft.flight_time
    FROM flight_main_table fmt
        INNER JOIN base_table bt
            ON fmt.base_id = bt.base_id
        INNER JOIN flight_cost_table fct
            ON fmt.flight_id = fct.flight_id
        INNER JOIN flight_timing_table ft
            ON fmt.flight_id = ft.flight_id
    WHERE fmt.base_id = 109

Upvotes: 2

FrustratedWithFormsDesigner
FrustratedWithFormsDesigner

Reputation: 27496

The error is trying to you that the subquery

select flight_id from flight_main_table where base_id=109

is returning more than one value. This is a problem because you tried to join c.flight_id to it using =, which means it will try to select one row from c based on the value on the right-hand side of =. If you want the possibility of multiple values, you need the in keyword. Try changing it to 

...
c.flight_id in (select flight_id from flight_main_table where base_id=109) and
d.flight_id in (select flight_id from flight_main_table where base_id=109)
...

if it makes sense for the subquery to return multiple values. If returning multiple values doesn't make sense, maybe it's time to make sure that your data is clean.

Upvotes: 0

billygoat
billygoat

Reputation: 21984

Hmm.. I am extremely confused. Your query could be very easily rewritten to avoid error, by changing the where clause.

where
a.base_id=109 and
b.base_id= a.base_id and
c.flight_id= b.flight_id and
d.flight_id=b.flight_id

There is no need for an Inner query.

Upvotes: 1

garlon4
garlon4

Reputation: 1270

In postgresql, you can add a LIMIT clause to your inner selects. That is, change

c.flight_id=(select flight_id from flight_main_table where base_id=109)

to

c.flight_id=(select flight_id from flight_main_table where base_id=109 limit 1)

or, if you want to match any of the values, you can do

c.flight_id in (select flight_id from flight_main_table where base_id=109)

Upvotes: -1

mdma
mdma

Reputation: 57707

It seems that a base_id can correspond to more than one flight_id. I don't know your requirements, so I can't say if that's right or wrong.

If you want multiple flight_id's per base_id, then change 

c.flight_id=(select flight_id from flight_main_table where base_id=109) and
d.flight_id=(select flight_id from flight_main_table where base_id=109)

to

c.flight_id IN (select flight_id from flight_main_table where base_id=109) and
d.flight_id IN (select flight_id from flight_main_table where base_id=109)

If you really want a unique flight_id per base_id then you'll have to change your model and data.

PS: You can use JOIN!

Upvotes: 0

janhink
janhink

Reputation: 5023

The problem is that the subquery (select flight_id from flight_main_table where base_id=109) returns more than one result which is not allowed when using = operator for the subquery's result. You can either use the keywork IN instead of =, or make sure the subquery contains just one result per base_id.

Upvotes: 2

NKCSS
NKCSS

Reputation: 2746

SELECT TOP 1 * FROM <tableName>

Upvotes: 0

Related Questions