CODEWITHSUNDEEP
sqlpostgresql
Greg Butler
Greg Butler

Reputation: 153

Postgresql subquery in expression returns more than one row

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.

Upvotes: 1

Views: 148

Answers (4)

Alex Yu
Alex Yu

Reputation: 3547

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

Upvotes: 1

Greg Butler
Greg Butler

Reputation: 153

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269773

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.

Upvotes: 2

Philippe
Philippe

Reputation: 1827

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

Upvotes: 0

Related Questions