CODEWITHSUNDEEP
sql
Jenny B
Jenny B

Reputation: 209

More than one row returned by a subquery in simple SQL

I have these tables:

person(pid, name,email,phone,city)

ride(rid,pid,date,spots,start,target) [rideID, personID- the person who gives the ride, spots= open slots in the ride,]

participate(pid,rid)- person pid participates in ride rid

I have to find the query findRidesForUser (pid,date)

which gives me the contact details of all the people who suggest a ride in the specific date that starts in a city where pid lives, e.g, where ride.start=pid.city.

I'm trying to use

"SELECT person.name, person.email, person.phone, person.city \
 FROM person WHERE pid=(\
    SELECT pid FROM ride WHERE date='%s' AND \
    ride.start= (SELECT city FROM person WHERE person.pid=pid))"

But it gives me the error: Error executing query: ERROR: more than one row returned by a subquery used as an expression

Upvotes: 0

Views: 2836

Answers (3)

Chamila Chulatunga
Chamila Chulatunga

Reputation: 4914

You should be looking to join the two tables on the appropriate keys:

SELECT
    p.name,
    p.email,
    p.phone,
    p.city
FROM person p
JOIN ride r
ON (p.pid = r.pid)
WHERE r.date = 'desiredDate'
AND r.start = (SELECT city FROM person WHERE pid = 'userPid')

Where 'desiredDate' and 'userPid' are the input parameters of findRidesForUser (pid,date)

Upvotes: 2

Ian Overton
Ian Overton

Reputation: 1060

using person.pid and pid is the same thing that's the same as saying 1=1. Also the pid= implies that you only want one result back, but your getting more then one so either use top or limit to limit the subquery to one or change the = to an 'in'. Using an in and fixing the sub-subquery is as follows:

"SELECT person.name, person.email, person.phone, person.city \
 FROM person WHERE pid in (\
    SELECT pid FROM ride WHERE date='%s' AND \
    ride.start= (SELECT city FROM person as person1 WHERE person.pid=person1.pid))"

Tho I think this is the same thing

"SELECT person.name, person.email, person.phone, person.city \
 FROM person WHERE pid in (\
    SELECT pid FROM ride WHERE date='%s' AND \
    ride.start= city)"

Upvotes: 2

Greg Oks
Greg Oks

Reputation: 2730

Try using "In" instead of using "="

Upvotes: 0

Related Questions