Database PostgreSQL subselect problem

Database PostgreSQL subselect problem,database,postgresql,Database,Postgresql,When I try to do the select statement, I always get the following error: ERROR: more than one row returned by a subquery used as an expression Only if there's only one result, it works. Why and how to fix? SELECT name from person p where id = ( select prs from leader where age(leader.lastcourse) > '1 year');

When I try to do the select statement, I always get the following error:

ERROR: more than one row returned by a subquery used as an expression

Only if there's only one result, it works. Why and how to fix?

SELECT name from person p where 
id = ( select prs from leader 
where age(leader.lastcourse) > '1 year');

#1

You are trying to assign one particular ID from a select that seems to be returning multiple ID's. OrbMan's solution will take the first ID, randomly, from the subquery. If you want ALL ID's that meet the subquery condition,

SELECT name from person p where  
id IN ( select prs from leader  
where age(leader.lastcourse) > '1 year'); 

Mark's approach to use a JOIN rather than subquery also works.


#2

It makes no sense to say that an integer is equal or not equal to a result set containing two rows. To do what you want you can choose between IN, EXISTS, or a JOIN. Here's how you can do it with a join:

SELECT name
FROM person p
JOIN leader
ON p.id = leader.prs
where age(leader.lastcourse) > '1 year'

#3

Why should there be a 'top 1'? Works only without it.

#4

@melbic: Because I made a cut and paste error :-) Fixed the post.