# leetcode\_1045

SQL架构 Table: Customer

+-------------+---------+ | Column Name | Type | +-------------+---------+ | customer\_id | int | | product\_key | int | +-------------+---------+ product\_key is a foreign key to Product table. Table: Product

+-------------+---------+ | Column Name | Type | +-------------+---------+ | product\_key | int | +-------------+---------+ product\_key is the primary key column for this table.

Write an SQL query for a report that provides the customer ids from the Customer table that bought all the products in the Product table.

For example:

Customer table: +-------------+-------------+ | customer\_id | product\_key | +-------------+-------------+ | 1 | 5 | | 2 | 6 | | 3 | 5 | | 3 | 6 | | 1 | 6 | +-------------+-------------+

Product table: +-------------+ | product\_key | +-------------+ | 5 | | 6 | +-------------+

Result table: +-------------+ | customer\_id | +-------------+ | 1 | | 3 | +-------------+ The customers who bought all the products (5 and 6) are customers with id 1 and 3.

## Solutions

1. **group by**

```sql
# Write your MySQL query statement below
SELECT customer_id
    FROM Customer
    GROUP BY customer_id
    HAVING COUNT(DISTINCT(product_key)) = (SELECT COUNT(*) FROM Product)
```