Page 1 of 1

Count from multiple tables

Posted: Thu Jul 09, 2009 2:24 pm
by RikComery
Hi. I have a mysql query that i need a bit of help with.

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";
 
Apart from being a bit messy, this clearly doesn't work.

Is anyone able to point me in the right direction?

Re: Count from multiple tables

Posted: Thu Jul 09, 2009 2:27 pm
by VladSun

Re: Count from multiple tables

Posted: Thu Jul 09, 2009 2:38 pm
by RikComery
Thanks for the speedy response, but maybe you could elaborate a little. My attempts at using DISTINCT have still resulted in just one row returned. I need one row for each product. (i.e. in my example, 2 rows).

One thing i should probably add. In some instances, a product may have no clients, and possible even no suppliers. I need the resulting table to still show the row, but place a zero in the Suppliers and Customers column. I don't want the row to be removed from the resultset.

Re: Count from multiple tables

Posted: Thu Jul 09, 2009 3:00 pm
by VladSun
Post some code, please :)

Re: Count from multiple tables

Posted: Thu Jul 09, 2009 3:20 pm
by RikComery
I think i have pretty much given you most of what i have. Here is the full script so far. Very little i know, but i have kinda fallen at the first hurdle.

Code: Select all

<table border="0" cellpadding="3">
    <thead>
        <tr>
            <td>Product Name</td>
            <td>Suppliers</td>
            <td>Customers</td>
        </tr>
    </thead>
    <tbody>
        <?php
            $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";
            
            $result = mysql_query($sql);
            
            while ($rs = mysql_fetch_array($result)) {
                print '<tr>
                    <td>' . $rs["productName"] . '</td>
                    <td>' . $rs["suppliers"] . '</td>
                    <td>' . $rs["customers"] . '</td>
                </tr>';
            }
        ?>
    </tbody>
</table>

Re: Count from multiple tables

Posted: Thu Jul 09, 2009 3:35 pm
by VladSun
OK, I think my advice doesn't make any sense :) Sorry about it.

Let me think for a minute ...

Re: Count from multiple tables

Posted: Thu Jul 09, 2009 3:47 pm
by VladSun
While I'm thinking (sorry it's very late in the night here) you may normalize your DB like this:

TABLE = PRODUCTS
--------------------
| id | productName |
--------------------
| 1 | product a |
| 2 | product b |
--------------------

TABLE = SUPPLIERS
-------------------------------
| id | supplierName |
-------------------------------
| 1 | supplier a |
| 2 | supplier b |
| 3 | supplier c |
| 4 | supplier d |
-------------------------------

TABLE = CUSTOMERS
---------------------------------
| id | customerName |
---------------------------------
| 1 | customer a |
| 2 | customer b |
| 3 | customer c |
| 4 | customer d |
---------------------------------

TABLE = SUPPLIED_PRODUCTS
-------------------------------
| FK_product_id | FK_supplier_id |
-------------------------------
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 1 |
| 2 | 4 |
-------------------------------

TABLE = CUSTIOMERS_BUYING_PRODUCTS
---------------------------------
| FK_customer_id | FK_product_id
---------------------------------
| 1 | 1 |
| 2 | 1 |
| 1 | 2 |
| 2 | 2 |
| 1 | 2 |
| 3 | 2 |
---------------------------------

PS: Check for errors, but that's the DB scheme you need

Re: Count from multiple tables

Posted: Thu Jul 09, 2009 3:56 pm
by VladSun
If you have this DB scheme (which is the right one to use in your case) then it's very easy and efficient to achive what you are after:
[sql]SELECT    product.name,    (        SELECT             count(supplied_product.FK_supplier_id)         FROM            supplied_product        WHERE             supplied_product.FK_product_id = product.id    ) AS supplier_product_count,    (        SELECT             count(CUSTIOMERS_BUYING_PRODUCTS.FK_customer_id)         FROM            CUSTIOMERS_BUYING_PRODUCTS        WHERE             FK_product_id = product.id    ) AS customer_product_countFROM      products[/sql]

PS: You need to create an INDEX for every ID or FK_* field you have.

Re: Count from multiple tables

Posted: Thu Jul 09, 2009 4:07 pm
by VladSun
One thing i should probably add. In some instances, a product may have no clients, and possible even no suppliers. I need the resulting table to still show the row, but place a zero in the Suppliers and Customers column. I don't want the row to be removed from the resultset.
So... which one should always be presented in the reports - the customer, the supplier or the product?

Re: Count from multiple tables

Posted: Thu Jul 09, 2009 4:16 pm
by VladSun
In fact, I should applogize to my self :)

Using COUNT(DISTINCT .....) together with LEFT JOIN should work indeed :)

Re: Count from multiple tables

Posted: Thu Jul 09, 2009 5:23 pm
by RikComery
Wow, it works exactly as it is. That is perfect.

Many thanks for your time.