Postgres. Inner join with 1 to many

Question

Need your help. I have 3 tables

products | id | title | price | .... |

product_to_category_list | id | product_id | category_id |

and "categories" | id | cat_title |

For each product in "products" table can be connected many rows in "product_to_category_list" 'cause one product can be related to different categories in the same time.

I need a query to get All products that is associated with a few certain categories + extra column with all categories for this product separated by commas.

For ex. I need a product that associated with two categories (3 and 5) :(product_to_category_list.category_id = 3 and product_to_category_list.category_id = 5)

select 
"products"."id", "title", "manufacturer", "amount", "price", 
"photo", "description", "available", "valid_until", "weight",
array_to_string(array_agg(product_to_category_list.category_id), ',') as category_id


from "products" inner join "product_to_category_list" on "products"."id" = "product_to_category_list"."product_id" 
where (category_id = 5 ) and 
(select 
count("title") from "products" inner join "product_to_category_list" on "products"."id" = "product_to_category_list"."product_id" 
 where (category_id =3)  ) >0


group by "products"."id"

ScreenShot 1 It returns me Almost the result that I need But: 1) category_id contains just value 5 2) query generates dynamically and it can be 3-5 categories for one product as condition, and I'm not sure that 5 inner selects are a good idea.

---

I designed another query:

select p.title,p.manufacturer, p.amount,p.price,p.photo,p.description, p.available, p.valid_until,p.weight, 
array_to_string(array_agg(c.category_id), ',') as cats_id
      from products p
      INNER JOIN product_to_category_list c ON c.product_id = p.id [[[where cats_id like "%paramCat_1%" and cats_id like "%paramCat_2%" ....  ]]]]
      group by p.id 

It returns: ScreenShot 2

And again it's almost what I need, but I can't use a new alias with Where and Like condition to compare it with my categories.

So, how can I get One row with all data from products and all categories from product_to_category_list.category_id associated with the products.id, separated by commas (in an extra field)

Hope for your help ;)

Answer 1

You could use a subselect from the product_to_category_list table to get a list of the product ids you need, and join your query to that.

SELECT p.title,p.manufacturer, p.amount,p.price,p.photo,p.description, p.available, p.valid_until,p.weight,
array_to_string(array_agg(c.category_id), ',') AS cats_id
FROM products p
INNER JOIN product_to_category_list c 
    ON c.product_id = p.id 
INNER JOIN (SELECT DISTINCT product_id FROM WP.product_to_category_list WHERE category_id IN (3,5) ) c_limit
    ON c_limit.product_id = p.id
GROUP BY p.title,p.manufacturer, p.amount,p.price,p.photo,p.description, p.available, p.valid_until,p.weight