CODEWITHSUNDEEP
databasepostgresql
Luis Henrique
Luis Henrique

Reputation: 771

Returned by a subquery

GOAL

I have a table A with column NAME, I would like that whenever column NAME of table A, is equal to column NAME of table B, is selected in column CODE_CLIENT of table B

QUERY

SELECT 
CASE WHEN name = (select name from tb_get_names) 
THEN (select code_client from tb_get_names) 
ELSE 'null' 
END AS Result FROM tb_get_clients

OUTPUT

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

Upvotes: 0

Views: 24

Answers (1)

forpas
forpas

Reputation: 164064

Each of the 2 subqueries you use, return all the names or all the code_clients of the table tb_get_names. This is why you get the error.
You need a LEFT JOIN:

SELECT 
  c.name,
  n.code_client AS Result
FROM tb_get_clients AS c LEFT JOIN tb_get_names AS n
ON n.name = c.name

Upvotes: 1

Related Questions