Liam Delahunty: Home Tips Web Contact
Recommended laptop
under £500.

Think I deserve a present? See my Amazon Wish List

MySQL Self Join

Given a table product_tbl with a field product_status we could see how many products there are and how many currently are not for sale with a query like the following. Notice the clause is in the ON of the join.

We are joining to the same table twice so we can query it under different conditions.

SELECT
count(p1.id) as total_products,
count(p2.id) as not_for_sale
FROM product_tbl p1
LEFT JOIN product_tbl p2
ON p1.id = p2.id AND p2.product_status = 0
 
+----------------+--------------+
| total_products | not_for_sale |
+----------------+--------------+
|           1024 |          210 |
+----------------+--------------+
1 row in set (0.10 sec)

Now lets make this harder with a real world example. Get all the categories and galleries and show how many products are listed in each gallery, and how many are not currently for sale (product_status = 0)

SELECT
category_tbl.id,
category_tbl.category,
gallery_tbl.id,
gallery_tbl.gallery,
count(p.id) as total_products,
count(p2.id) as hidden
FROM category_tbl
INNER JOIN category_gallery_tbl
ON category_tbl.id = category_gallery_tbl.category_id
INNER JOIN gallery_tbl
ON category_gallery_tbl.gallery_id = gallery_tbl.id
INNER JOIN gallery_product_tbl
ON gallery_product_tbl.gallery_id = gallery_tbl.id
INNER JOIN product_tbl p
ON gallery_product_tbl.product_id = p.id
LEFT JOIN product_tbl p2
ON gallery_product_tbl.product_id = p2.id AND p2.product_status = 0
GROUP BY gallery_tbl.id
ORDER BY category, gallery;
 
+----+------------------+----+------------------------------------+----------------+--------+
| id | category         | id | gallery                            | total_products | hidden |
+----+------------------+----+------------------------------------+----------------+--------+
|  3 | Accoutrements    |  1 | Action Figures                     |              9 |      4 |
|  3 | Accoutrements    | 55 | Anatomy                            |              6 |      5 |
|  3 | Accoutrements    | 24 | Auto                               |             19 |     11 |
|  3 | Accoutrements    | 61 | Bags                               |              5 |      5 |
|  3 | Accoutrements    | 64 | Bathroom                           |              3 |      0 |
|  2 | Moleskine        | 32 | Classic Large                      |              6 |      0 |
|  2 | Moleskine        | 33 | Classic Pocket                     |              8 |      0 |
|  2 | Moleskine        | 31 | Diaries                            |              4 |      0 |
|  2 | Moleskine        | 34 | Moleskine Assortment               |              3 |      0 |
|  4 | Natural Products | 77 | Ach Brito                          |             67 |      0 |
|  4 | Natural Products | 78 | Aquis                              |             35 |      0 |
|  4 | Natural Products | 80 | Cheeky Chimp                       |             51 |      0 |
|  4 | Natural Products | 81 | Confianca                          |             45 |      0 |
|  1 | Worldwide co     | 42 | Bathroom                           |             15 |      0 |
|  1 | Worldwide co     | 76 | Bedroom                            |              5 |      0 |
|  1 | Worldwide co     | 46 | Candles                            |              6 |      0 |
|  1 | Worldwide co     | 44 | Magnets                            |             10 |      0 |
+----+------------------+----+------------------------------------+----------------+--------+

Share this!