How to get all rows from one table which have all relations?-CodePudding

I have 3 tables:

companies (id, name)
union_products (id, name)
products (id, company_id, union_product_id, price_per_one_product)

I need to get all companies which have products with union_product_id in (1,2) and total price of products (per company) is less than 100.

What I am trying to do now:

select * from "companies" where exists
    (
    select id from "products"
             where "companies"."id" = "products"."company_id"
               and "union_product_id" in (1, 2)
             group by id
             having COUNT(distinct union_product_id) = 2 AND SUM(price_per_one_product) < 100
)

The problem I stuck with is that I'm getting 0 rows from the query above, but it works if I'll change COUNT(distinct union_product_id) = 2 to 1.

DB fiddle: https://www.db-fiddle.com/f/iRjfzJe2MTmnwEcDXuJoxn/0

CodePudding user response：

Try to join the three tables as the following:

SELECT C.id, C.name FROM 
products P JOIN union_products U
ON P.union_product_id=U.id
JOIN companies C
ON P.company_id=C.id
WHERE P.union_product_id IN (1, 2)
GROUP BY C.id, C.name
HAVING COUNT(DISTINCT P.union_product_id) = 2 AND
       SUM(P.price_for_one_product) < 100
ORDER BY C.id

See a demo.

CodePudding user response：

SELECT c.name FROM "companies" c 
JOIN "products" p ON c.id = p.company_id 
WHERE union_product_id IN (1, 2) AND price_for_one_product < 100
GROUP BY c.name
HAVING COUNT(DISTINCT p.name) =2

This would provide you all the company(s) name(s) which has provides both union_product_id 1 and 2 and the price_for_one_product/ price_per_one_product is less than 100.

Note: You might need to change price_for_one_product with price_per_one_product, as in question you have used price_per_one_product but db-fiddle link table defination has used price_for_one_product.