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

Question

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

I would sure hope they're not still teaching the old style joins in schools. — Joe Stefanelli
– Joe Stefanelli, Commented Apr 8, 2011 at 19:43
It looks like you have more than 1 entry in flight_main_table where base_id = 109. The quick fix would be to add a ' LIMIT 1' to the end of each subquery, but you should probably reevaluate your database structure. — Sam Dufel
– Sam Dufel, Commented Apr 8, 2011 at 19:45

Intekhab Khan · Accepted Answer · 2011-04-08 20:04:06Z

2

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

edited Apr 8, 2011 at 20:04

Intekhab Khan

1,8734 gold badges19 silver badges29 bronze badges

answered Apr 8, 2011 at 19:51

Joe Stefanelli

136k21 gold badges243 silver badges241 bronze badges

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

1 Comment

Intekhab Khan Over a year ago

thanks this works Except two more rows are coming.But that is the problem of data the table have .Once again thanks :)

janhink · Accepted Answer · 2011-04-08 19:46:18Z

2

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.

answered Apr 8, 2011 at 19:46

janhink

5,0333 gold badges31 silver badges38 bronze badges

Comments

billygoat · Accepted Answer · 2011-04-08 19:49:33Z

1

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.

answered Apr 8, 2011 at 19:49

billygoat

22.1k5 gold badges43 silver badges50 bronze badges

1 Comment

Intekhab Khan Over a year ago

-This is also working fine Giving the same result which suggested by joe in upper post "INNER JOINS"

NKCSS · Accepted Answer · 2011-04-08 19:46:00Z

0

SELECT TOP 1 * FROM <tableName>

answered Apr 8, 2011 at 19:46

NKCSS

2,7561 gold badge23 silver badges38 bronze badges

1 Comment

FrustratedWithFormsDesigner Over a year ago

There should at least be some ordering clause or you never know for sure which value you'll get back.

mdma · Accepted Answer · 2011-04-08 19:47:16Z

0

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!

answered Apr 8, 2011 at 19:47

mdma

57.9k12 gold badges97 silver badges128 bronze badges

Comments

FrustratedWithFormsDesigner · Accepted Answer · 2011-04-08 19:59:27Z

0

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.

edited Apr 8, 2011 at 19:59

answered Apr 8, 2011 at 19:46

FrustratedWithFormsDesigner

27.7k31 gold badges151 silver badges211 bronze badges

Comments

garlon4 · Accepted Answer · 2011-04-08 19:48:22Z

-1

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)

answered Apr 8, 2011 at 19:48

garlon4

1,28011 silver badges15 bronze badges

1 Comment

FrustratedWithFormsDesigner Over a year ago

OP has already stated they are using MS SQL Server, not PostgreSQL.

Collectives™ on Stack Overflow

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

7 Answers 7

1 Comment

Comments

1 Comment

1 Comment

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

1 Comment

Comments

1 Comment

1 Comment

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related