How to combine mulltiple IN clause in Postgresql?

Question

I have two tables: goods with column id and title filter with column id And one cross-join table: filter_good with columns filterid and goodid I need to find goods that match any filter in one list of filters ids and also match any filter in another list of filters ids. I'm trying to achieve it using this query without luck:

SELECT
    goods.*
FROM
    goods
JOIN filter_good ON goods.id = filter_good.goodid
WHERE filter_good.filterid IN (3)
AND filter_good.filterid IN (1, 2)

How this can be done?

I need to return records that match only if there is a record with id 3 AND with id 1 or 2

Answer 1

Well, your question is really not that clear but you problably need an EXISTS clause:

SELECT
    *
FROM
    goods
WHERE 
  EXISTS (
    SELECT * 
    FROM filter_good 
    WHERE goods.id = filter_good.goodid
      AND filter_good.filterid IN (3)
  )
  AND EXISTS (
    SELECT * 
    FROM filter_good 
    WHERE goods.id = filter_good.goodid
      AND filter_good.filterid IN (1,2)
  )