PostgreSQL Subquery Task
When I try to execute a select statement, I always get the following error:
ERROR: more than one row returned by subquery used as expression
Only if there is only one result does it work. Why and how to fix it?
SELECT name from person p where
id = ( select prs from leader
where age(leader.lastcourse) > '1 year');
+2
a source to share
2 answers
You are trying to assign one specific ID from the selected one, which seems to return multiple IDs. OrbMan's solution will take the first identifier, randomly, from a subquery. If you want the ALL id to match the subquery condition,
SELECT name from person p where
id IN ( select prs from leader
where age(leader.lastcourse) > '1 year');
Also Mark's approach of using JOIN rather than subquery works.
+5
a source to share
It makes no sense to say that an integer is equal or not equal to a result set containing two strings. To do what you want, you can choose between IN, EXISTS, or JOIN. Here's how you can do it with a connection:
SELECT name
FROM person p
JOIN leader
ON p.id = leader.prs
where age(leader.lastcourse) > '1 year'
+4
a source to share