SQL query with multiple "IN" on same many-to-many table

Question

Hello,

I've got m2m relationship like this:

#main table
CREATE TABLE products_product (
    id integer NOT NULL,
    company_id integer,
    user_id integer,
    type_id integer NOT NULL,
    name character varying(100) NOT NULL,
    description character varying(200) NOT NULL,
    tags character varying(255) NOT NULL,
    image character varying(200) NOT NULL
);
#intermediate table
CREATE TABLE products_ingridientbound (
    id integer NOT NULL,
    ingridient_id integer NOT NULL,
    company_id integer NOT NULL,
    price double precision NOT NULL,
    active boolean NOT NULL,
    "asTopping" boolean NOT NULL
);
#final m2m table
CREATE TABLE products_ingridientproductbound (
    id integer NOT NULL,
    product_id integer NOT NULL,
    ingridient_id integer NOT NULL,
    "optionValue" integer NOT NULL,
    CONSTRAINT "products_ingridientproductbound_optionValue_check" CHECK (("optionValue" >= 0))
);

All I want to do is to get products, which has 2 (in this example) ingridient groups, one with ID in range (16, 17, 18, 19), and another in range (43, 44, 45). I want ingridient ID to be in both groups simultaneously.

My query looks like this (it's actually generated by django orm):

SELECT "products_product"."id","products_product"."name",
FROM "products_product"
INNER JOIN "products_ingridientproductbound" 
ON ("products_product"."id" = "products_ingridientproductbound"."product_id")
WHERE ("products_ingridientproductbound"."ingridient_id" IN (16, 17, 18, 19)
AND "products_ingridientproductbound"."ingridient_id" IN (43, 44, 45)) LIMIT 21

It gives me 0 results, but if I run query with only one group of IN queries than it works!

Here is data in my "products_ingridientproductbound" table. I thought that my query could return product 3, as it is in both groups (16 and 45), but now I'm confused a bit. Screenshot of phpPgAdmin

Answer 1

You ask in your two IN clauses that the same field is in two sets without common elements. Therefore you will always get a false in one of the clauses, hence your AND will be false.

Answer 2

If I understand what you are after, doing an INNER JOIN is not what you are looking for here, I would use a subquery instead.

You want the product id and name that have both ingredients, correct?

I didn't test this statement but it should be something like this:

SELECT "products_product"."id","products_product"."name",
FROM "products_product"
WHERE (
    EXISTS (SELECT 1 FROM "products_ingridientproductbound" WHERE "products_product"."id" = "products_ingridientproductbound"."product_id" AND "products_ingridientproductbound"."ingridient_id" IN (16, 17, 18, 19))
     AND
    EXISTS (SELECT 1 FROM "products_ingridientproductbound" WHERE "products_product"."id" = "products_ingridientproductbound"."product_id" AND "products_ingridientproductbound"."ingridient_id" IN (43, 44, 45))
)
LIMIT 21

Edit: remove silly remark about combining, thanks jvanderh.

Answer 3

txwikinger is right. If you want to filter product that have two related ingridientbounds you need to have two JOINs in the query like this:

SELECT "products_product"."id","products_product"."name",
FROM "products_product"
INNER JOIN "products_ingridientproductbound" ig1
ON ("products_product"."id" = ig1."product_id")
INNER JOIN "products_ingridientproductbound" ig2
ON ("products_product"."id" = ig2."product_id")
WHERE (ig1."ingridient_id" IN (16, 17, 18, 19)
AND ig2."ingridient_id" IN (43, 44, 45)) 
LIMIT 21

Answer 4

In SQL Server you can use INTERSECT like this

SELECT "products_product"."id","products_product"."name",
FROM "products_product"
INNER JOIN "products_ingridientproductbound" 
ON ("products_product"."id" = "products_ingridientproductbound"."product_id")
WHERE ("products_ingridientproductbound"."ingridient_id" IN (16, 17, 18, 19)

INTERSECT 

SELECT "products_product"."id","products_product"."name",
FROM "products_product"
INNER JOIN "products_ingridientproductbound" 
ON ("products_product"."id" = "products_ingridientproductbound"."product_id")
WHERE ("products_ingridientproductbound"."ingridient_id" IN (43, 44, 45)

Meaning return rows that are in both queries. Check if your DB has that option.