Given the above tables definition, we would like to find customers who ordered more than two product type.
Solution:
Here we have to join three tables to get the result
Since the relationship between a customer and product is many-to-many relationship, we have orders table as a third table to handle this complex relationship
And when we want data from both tables, we have to connect the three tables together as follows
SELECT c.custname, p.prodname
FROM customer c
INNER JOIN orders o ON c.custid = o.custid
INNER JOIN product p on p.prodid = o.prodid
| Custname | ProdName |
| John | Laptop |
| Doe | Tablet |
| Smith | Coat |
| John | Chair |
| Doe | Ball |
| Smith | Jacket |
| John | T-Shirt |
This will give us all the customer who ordered items and what orders they ordered.
But if we we see the result, we will see that there are multiple rows for the same customer if he ordered several products.
In this case, we can use SQL group by with aggregate functions, to count the number of items he purchased.
SELECT c.custid, c.custname, COUNT(o.prodid) as products_count
FROM customer c
INNER JOIN orders o ON c.custid = o.custid
INNER JOIN product p on p.prodid = o.prodid
GROUP BY c.custid, c.custname
| custid | custname | product count |
| 1 | John | 3 |
| 2 | Smith | 2 |
| 3 | Doe | 2 |
Finally, we can filter the result returned by the group by by using having clause to count if the customer purchased more than n number of items. In this case n=2
| custid | custname | product count |
| 1 | John | 3 |
SELECT c.custid, c.custname, COUNT(o.prodid) as products_count
FROM customer c
INNER JOIN orders o ON c.custid = o.custid
INNER JOIN product p on p.prodid = o.prodid
GROUP BY c.custid, c.custname
HAVING COUNT(o.prodid) > 2