Search code examples
sqlpostgresqlleft-joinunique

SQL Join Having Group By Count

I am new to SQL and databases and am trying to learn about queries and the different relationship types by challenging myself.

Many To Many Relationship

I am not sure if I drew this correctly but I basically have two tables. Supplier and Brand. One brand can have many suppliers but one supplier may also supply to many brands.

I created a third table which holds these relationships which is called Supplier_Brand.

SELECT supplier_name, brand.brand_name
FROM Brand
INNER JOIN Supplier_Brand
ON Brand.brand_id = Supplier_Brand.brand_id
INNER JOIN Supplier
ON Supplier.supplier_id = Supplier_Brand.supplier_id;

I managed to join them with the query above and get the following output:

However, I would like to only show the supplier that delivers to more than one brand ( what is shown in the green box ) I have tried all sort of things with GROUP BY and HAVING and count but I am not able to get it right. How could I solve this?

Only the green

Solution

  • You can use CTE (Common Table Expression) to achieve your expectation.

    WITH id_of_multiple_brand AS (SELECT supplier_id
    FROM Brand
    INNER JOIN Supplier_Brand
    ON Brand.brand_id = Supplier_Brand.brand_id
    INNER JOIN Supplier
    ON Supplier.supplier_id = Supplier_Brand.supplier_id
    GROUP BY supplier_id
    HAVING count(brand.brand_name) > 1)
SELECT supplier_name, brand.brand_name
FROM Brand
INNER JOIN Supplier_Brand
ON Brand.brand_id = Supplier_Brand.brand_id
INNER JOIN Supplier
ON Supplier.supplier_id = Supplier_Brand.supplier_id
where supplier_id in (select supplier_id from id_of_multiple_brand);