CODEWITHSUNDEEP
sqlpostgresql
alexanoid
alexanoid

Reputation: 25832

PostgreSQL ANY IN subselect result

I'm trying to write the following query:

SELECT DISTINCT t.* FROM tasks t  WHERE (769, 2) IN 
  (SELECT c3.id FROM categories c3 JOIN tasks_categories tc ON c3.id = tc.category_id WHERE tc.task_id = t.id)

but it fails with the following error:

ERROR: subquery has too few columns

looks like the reason is that I'm trying to check (769, 2) in the result of subquery. How to rewrite this query in order to check:

ANY of (769, 2) in the result of (subselect) ?

Upvotes: 0

Views: 385

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270301

You can use exists . . . and probably don't need select distinct:

SELECT t.*
FROM tasks t
WHERE EXISTS (SELECT 1
              FROM categories c3 JOIN
                   tasks_categories tc
                   ON c3.id = tc.category_id
              WHERE tc.task_id = t.id AND c3.id IN (769, 2)
             );

SELECT DISTINCT just incurs additional overhead -- and should not be needed assuming that tasks has no duplicate rows.

Upvotes: 1

Jim Macaulay
Jim Macaulay

Reputation: 5155

You have to rewrite the query in below format,

   SELECT DISTINCT t.* FROM tasks t
JOIN tasks_categories tc ON (tc.task_id = t.id)
JOIN categories c3 ON (c3.id = tc.category_id)
where  c3.id in (769, 2);

Upvotes: 2

Related Questions