CODEWITHSUNDEEP
sqlpostgresql
yacine hachmi
yacine hachmi

Reputation: 3

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

When trying select columns from table A with select subquery that takes id column from table B I get an error

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

Thanks for your help.

SELECT 
    id, column_1, column_2, column_3, 
    (SELECT id FROM table_b AS b 
     WHERE b.column_1 = table_a.column_1 
       AND b.column_2 = table_a.column_2 
       AND b.column_3 = table_a.column_3) as b_id 
FROM 
    table_a;

Upvotes: 0

Views: 90

Answers (1)

Bohemian
Bohemian

Reputation: 424993

If you're getting multiple rows from a subquery that's returning multiple rows, you need a way to pick a winner. The easiest and most portable way is to aggregate, often using MAX():

SELECT 
    id,
    column_1,
    column_2,
    column_3, 
    (SELECT MAX(id) FROM table_b AS b 
     WHERE b.column_1 = table_a.column_1 
       AND b.column_2 = table_a.column_2 
       AND b.column_3 = table_a.column_3) as b_id 
FROM table_a

You can use MIN() too or even AVG() for non-id numeric values - whichever suits for your context.

Upvotes: 0

Related Questions