Count from multiple tables

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
RikComery
Forum Newbie
Posts: 5
Joined: Sun Jun 29, 2008 4:22 pm

Count from multiple tables

Post 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?
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Count from multiple tables

Post by VladSun »

There are 10 types of people in this world, those who understand binary and those who don't
RikComery
Forum Newbie
Posts: 5
Joined: Sun Jun 29, 2008 4:22 pm

Re: Count from multiple tables

Post 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.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Count from multiple tables

Post by VladSun »

Post some code, please :)
There are 10 types of people in this world, those who understand binary and those who don't
RikComery
Forum Newbie
Posts: 5
Joined: Sun Jun 29, 2008 4:22 pm

Re: Count from multiple tables

Post 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>
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Count from multiple tables

Post by VladSun »

OK, I think my advice doesn't make any sense :) Sorry about it.

Let me think for a minute ...
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Count from multiple tables

Post 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
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Count from multiple tables

Post 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.
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Count from multiple tables

Post 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?
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Count from multiple tables

Post by VladSun »

In fact, I should applogize to my self :)

Using COUNT(DISTINCT .....) together with LEFT JOIN should work indeed :)
There are 10 types of people in this world, those who understand binary and those who don't
RikComery
Forum Newbie
Posts: 5
Joined: Sun Jun 29, 2008 4:22 pm

Re: Count from multiple tables

Post by RikComery »

Wow, it works exactly as it is. That is perfect.

Many thanks for your time.
Post Reply