Mysql duplicate results of query relation with 2 other table , IN operator

I have 3 main table : products, stores and categories

• product relate many-to-many with stores via table product_store

• product relate many-to-many with categories via table product_category

How can i query all products, belongs a store ( with store_id : 1 ) and belongs one or many category ( with category_id in [ 4,5,6 ] ? I used below syntax, but result has duplicated product records (for example, product in both category 5 and 6, will show 2 times). Can I get it unique ?

select 
  "products"."name" 
from 
  "products" 
  inner join "product_categories" as "categories_join" on "categories_join"."product_id" = "products"."id" 
  inner join "categories" on "categories_join"."category_id" = "categories"."id" 
  inner join "product_stores" as "stores_join" on "stores_join"."product_id" = "products"."id" 
  inner join "stores" on "stores_join"."store_id" = "stores"."id" 
where 
  "categories"."id" in ( 4,5,6,7 ) 
  and "stores"."id" = 1

Answer

You can use select distinct to return only one product. However, you can also simplify your query by removing a join. The stores and categories tables are not needed, because you are only using the ids and these are available in the junction tables. I would also use simpler table aliases:

select distinct p."name" 
from "products" p join
     "product_categories" pc
     on pc."product_id" = p."id" join
     "product_stores" ps
     on ps."product_id" = p."id" 
where pc.category_id in ( 4,5,6,7 ) and
      ps."id" = 1;

Note: You should avoid using double quotes for identifiers. If your tables are defined using them, then I would suggest fixing the table definitions.

This query, in turn, would probably be more efficient using exists because select distinct would no longer be necessary:

select p."name" 
from "products" p
where exists (select 1
              from "product_categories" pc
              where pc."product_id" = p."id" andf
                    pc.category_id in ( 4,5,6,7 ) 
             ) and
      exists (select 1
              from "product_stores" ps
              where ps."product_id" = p."id" and
                    ps."id" = 1
             );