Postgresql subquery in expression returns more than one row

Question

I'm trying to map from a string value to a list of ids to use in an IN clause and haven't found a way to make it work yet. Hopefully the query I've tried so far will make this clear:

SELECT COUNT(*) FROM events
WHERE level_id IN (
  SELECT
  CASE
      WHEN skill_level='Beginner' THEN (SELECT id from levels WHERE code IN ('L1', 'L2'))
      WHEN skill_level='Intermediate' THEN (SELECT id from levels WHERE code IN ('L3', 'L4'))
      WHEN skill_level='Advanced' THEN (SELECT id from levels WHERE code IN ('L5', 'L6'))
  END ids
  FROM users WHERE id=2
)

Which could simplify for a 'Beginner' skill level user to this:

SELECT COUNT(*) FROM events WHERE level_id IN (1, 2)

I created a fiddle to test this here.

I get the error "more than one row returned by a subquery used as an expression" when I try this. I've tried a number of permutations including the array() operator but I always get one error or another.

Maybe I'm fundamentally going about this the wrong way. If anyone can suggest a fix for my query, or another way to accomplish the same thing it would be greatly appreciated.

Sample data would really help your question here.

Tim Biegeleisen
– Tim Biegeleisen

2021-06-28 13:42:03 +00:00
Commented Jun 28, 2021 at 13:42 — Tim Biegeleisen
– Tim Biegeleisen, Commented Jun 28, 2021 at 13:42
There is sample data in the linked fiddle.

Greg Butler
– Greg Butler

2021-06-29 13:01:40 +00:00
Commented Jun 29, 2021 at 13:01 — Greg Butler
– Greg Butler, Commented Jun 29, 2021 at 13:01

RubioRic · Accepted Answer · 2021-06-28 14:28:40Z

2

Don't use a case like that. Use simpler boolean logic:

SELECT COUNT(*)
FROM events
WHERE (skill_level = 'Beginner' AND level_id IN ('L1', 'L2')) OR
      (skill_level = 'Intermediate' AND level_id IN ('L3', 'L4')) OR
      (skill_level = 'Advanced' AND level_id IN ('L5', 'L6'));

I'm not sure what this logic is for:

FROM users WHERE id = 2

Your question does not explain that.

edited Jun 28, 2021 at 14:28

RubioRic

2,4684 gold badges32 silver badges38 bronze badges

answered Jun 28, 2021 at 14:08

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Greg Butler Over a year ago

"skill_level" is in the users table, while "code" is in the levels table. Please see the fiddle for clarification.

Gordon Linoff Over a year ago

@GregButler . . . You have already been asked for sample data. The question should clarify such things.

Alex Yu · Accepted Answer · 2021-06-30 12:16:57Z

Error "more than one row returned by a subquery used as an expression" arises because of:

    WHEN skill_level=... THEN (SELECT id from levels WHERE code IN (...))

inside CASE statement.

UPDATE

Maybe the best would be to use suggestion of @gordon-linoff



SELECT COUNT(*) 
FROM 
    events E
    JOIN levels L ON E.level_id = L.id
    JOIN users U ON 
        ( U.skill_level='Beginner' AND  L.code IN ('L1', 'L2'))
        OR  (U.skill_level='Intermediate' AND L.code IN ('L3', 'L4'))
        OR  (U.skill_level='Advanced' AND L.code IN ('L5', 'L6'))
   WHERE
       U.id = 2

https://www.db-fiddle.com/f/tP8MPuy2ks3Zfuo9jSAmJA/4

Previous answer

Fix would be to use CASE-statement as predicate for JOIN:

SELECT COUNT(*) FROM events 
WHERE
    level_id IN (
      SELECT 
              L.id
          FROM 
              users U
              JOIN levels L ON
                CASE
                    WHEN skill_level='Beginner' THEN  code IN ('L1', 'L2')
                    WHEN skill_level='Intermediate' THEN  code IN ('L3', 'L4')
                    WHEN skill_level='Advanced' THEN code IN ('L5', 'L6')
                END 
          WHERE
              U.id = 2
    )

Look on: https://www.db-fiddle.com/f/tP8MPuy2ks3Zfuo9jSAmJA/1

Greg Butler · Accepted Answer · 2021-06-29 20:59:10Z

1

I found a way that works in Postgresql 11. Didn't work in 9.4, but as I am working in 11, that's fine. I don't know if it's a good way, but it does work. Alex Yu's answer might be better but I haven't done any performance testing. Here it is for what it's worth:

SELECT COUNT(*) FROM events
WHERE level_id = ANY(ARRAY(
SELECT
CASE
    WHEN skill_level='Beginner' THEN ARRAY(SELECT id from levels WHERE code IN ('L1', 'L2'))
    WHEN skill_level='Intermediate' THEN ARRAY(SELECT id from levels WHERE code IN ('L3', 'L4'))
    WHEN skill_level='Advanced' THEN ARRAY(SELECT id from levels WHERE code IN ('L5', 'L6'))
END ids
FROM users WHERE id=2
));

count
3

View on DB Fiddle

answered Jun 29, 2021 at 20:59

Greg Butler

1632 silver badges7 bronze badges

2 Comments

Alex Yu Over a year ago

You found a good answer. Essentially it's the same as mine but has less letters. Actually @GordonLinoff maybe gave the best answer

Alex Yu Over a year ago

But maybe the best answer would be: db-fiddle.com/f/tP8MPuy2ks3Zfuo9jSAmJA/4

Philippe · Accepted Answer · 2021-06-28 14:10:34Z

0

It misses a key in levels table. You should add a skill_level column to link skill_level users. Then it becomes easier to make your query.

See the example here

answered Jun 28, 2021 at 14:10

Philippe

1,8376 silver badges19 bronze badges

1 Comment

Greg Butler Over a year ago

Thanks for the suggestion but I have to work with the tables as they are currently structured. This is a simplified example that demonstrates my problem, not my actual real-world tables.

Collectives™ on Stack Overflow

Postgresql subquery in expression returns more than one row

4 Answers 4

2 Comments

Comments

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related