SQL Join Having Group By Count-CodePudding

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

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?

CodePudding user response：

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);

CodePudding user response：

We can use MIN() and MAX() here as analytic functions:

WITH cte AS (
    SELECT s.supplier_name, b.brand_name,
           MIN(b.brand_name) OVER (PARTITION BY s.supplier_name) AS min_brand_name,
           MAX(b.brand_name) OVER (PARTITION BY s.supplier_name) AS max_brand_name
    FROM Brand b
    INNER JOIN Supplier_Brand sb ON b.brand_id = sb.brand_id
    INNER JOIN Supplier s ON s.supplier_id = sb.supplier_id
)

SELECT supplier_name, brand_name
FROM cte
WHERE min_brand_name <> max_brand_name
ORDER BY supplier_name, brand_name;