Assume i have the following tables. Table 1 contains a list of products. Table 2 contains a list of suppliers for each product and table 3 contains a list of customers who bought each product.
TABLE = PRODUCTS
--------------------
| id | productName |
--------------------
| 1 | product a |
| 2 | product b |
--------------------
TABLE = SUPPLIERS
-------------------------------
| id | productId | supplierName |
-------------------------------
| 1 | 1 | supplier a |
| 2 | 1 | supplier b |
| 3 | 1 | supplier c |
| 4 | 2 | supplier a |
| 5 | 2 | supplier d |
-------------------------------
TABLE = CUSTOMERS
---------------------------------
| id | productId | customerName |
---------------------------------
| 1 | 1 | customer a |
| 2 | 1 | customer b |
| 3 | 2 | customer a |
| 4 | 2 | customer b |
| 5 | 2 | customer c |
| 6 | 2 | customer d |
---------------------------------
What i would like to do is display a table on my page showing each product and how how many suppliers and customers each has. For example, with the above tables i would get:
product | suppliers | customers
-----------------------------------
product a | 3 | 2
product b | 2 | 4
The query i have been playing with is
Code: Select all
$sql = "SELECT p.productName, COUNT(s.productId) AS suppliers, COUNT(c.productId) AS customers";
$sql .= " FROM products p, suppliers s, customers c";
$sql .= " WHERE p.productId = s.productId";
$sql .= " AND p.productId = c.productId";
Is anyone able to point me in the right direction?