Postgres array query

(This basically elaborates on @valex's answer, though I didn't realise that until I posted; please accept @valex's not this one).

This can be done using only joins and aggregation.

Build a join tree mapping locations to products, as normal. Then join it with the list of desired products (one-column values rows) and filter the join to only matching product names. You now have one row with the location of a product wherever that product can be found.

Now group by location and return locations where the number of products present is equal to the number we're looking for (for ALL). For ANY we omit the HAVING filter because any location row returned by the join is what we want.

So:

WITH wantedproducts(productname) AS (VALUES('Volvo'), ('Lamborghini'))
SELECT l."ID"
FROM locations l
INNER JOIN locations_categories lc ON (l."ID" = lc."LocationID")
INNER JOIN categories c ON (c."ID" = lc."CategoryID")
INNER JOIN products_categories pc ON (pc."CategoryID" = c."ID")
INNER JOIN products p ON (p."ID" = pc."ProductID")
INNER JOIN wantedproducts wp ON (wp.productname = p."Name")
GROUP BY l."ID"
HAVING count(DISTINCT p."ID") = (SELECT count(*) FROM wantedproducts);

is what you want, basically.

For "stores with any of the wanted products" queries, drop the HAVING clause.

You an also ORDER BY the aggregate if you want to show stores with any match but sort based on number of matches.

You can also add a string_agg(p."Name") to the SELECT values-list if you want to list products that can be found at that store.

If you want your input to be an array rather than a values-list, just replace the VALUES (...) with SELECT unnest($1) and pass your array as the parameter $1, or write it literally in place of $1.

ANSWER IN PROGRESS: (I will add answers as I get the required result)

For your first question:

A customer wants to buy a Ferrari. This would be available from stores in categories 10 or 30. This gives us stores 100, 300 and 400 but not 200.

SELECT DISTINCT l.id, l.name
FROM Products p
LEFT JOIN Product_Categories p_c
ON p.id = p_c.ProductId
LEFT JOIN Categories c
ON p_c.CategoryId = c.id
LEFT JOIN Locations_Categories l_c
ON c.id = l_c.CategoryId
LEFT JOIN Locations l
ON l_c.LocationId = l.id
WHERE p.id = 1

Second question:

However, if a customer wants to buy a Volvo and a Lamborghini this would be available from stores in categories 10 and 20. Which only gives us store 400.

SELECT DISTINCT l.id, l.name
FROM Products p
LEFT JOIN Product_Categories p_c
ON p.id = p_c.ProductId
LEFT JOIN Categories c
ON p_c.CategoryId = c.id
LEFT JOIN Locations_Categories l_c
ON c.id = l_c.CategoryId
LEFT JOIN Locations l
ON l_c.LocationId = l.id
WHERE l.id in (select id
               from locations loc
               join locations_categories locat1              
               on loc.id = locat1.LocationId
               join locations_categories locat2
               on loc.id = locat2.LocationId
               where locat1.CategoryId = 10
               AND locat2.categoryId = 20)

RESULT FOR SECOND QUESTION USING INTERSECT: intersect will cross reference all the stores where 1 product can be found each time:

SELECT DISTINCT l.id, l.name
FROM Products p
LEFT JOIN Product_Categories p_c
ON p.id = p_c.ProductId
LEFT JOIN Categories c
ON p_c.CategoryId = c.id
LEFT JOIN Locations_Categories l_c
ON c.id = l_c.CategoryId
LEFT JOIN Locations l
ON l_c.LocationId = l.id
WHERE p.id = 2
INTERSECT
SELECT DISTINCT l.id, l.name
FROM Products p
LEFT JOIN Product_Categories p_c
ON p.id = p_c.ProductId
LEFT JOIN Categories c
ON p_c.CategoryId = c.id
LEFT JOIN Locations_Categories l_c
ON c.id = l_c.CategoryId
LEFT JOIN Locations l
ON l_c.LocationId = l.id
WHERE p.id = 3

For every new product you add a new INTERSECT statement and create a new select with the wanted product id SQLFIDDLE: http://sqlfiddle.com/#!15/ce97d/15

Well, it's hard to totally avoid arrays here but I think I found a solution with less array functions.

Instead of selecting needed locations, I excluded non valid ones.

WITH needed_categories AS (
  SELECT p."ID", array_agg(pc."CategoryID") AS at_least_one_should_match
  FROM Products p
  JOIN Products_Categories pc ON p."ID" = pc."ProductID"
  WHERE p."ID" IN (1, 3)
  GROUP BY p."ID"
),
not_valid_locations AS (
  SELECT DISTINCT lc."LocationID", unnest(nc.at_least_one_should_match)
  FROM Locations_Categories lc
  JOIN needed_categories nc ON NOT ARRAY[lc."CategoryID"] && nc.at_least_one_should_match 
  EXCEPT
  SELECT * FROM Locations_Categories
) 
SELECT * 
FROM Locations
WHERE "ID" NOT IN (
  SELECT "LocationID" FROM not_valid_locations
);

Here is the SQLFiddle: http://sqlfiddle.com/#!15/e138d/78

This works but I'm still trying to avoid double seq scan of the Location_Categories. The fact that cars can belong to multiple categories is a bit tricky, I solved this using arrays but I'm trying to get rid of these too.