Left join and belongs to

Question

I have three tables: products (id, name), categories (id, name) and products_categories (product_id, category_id).

Each product belongs to one or more categories.

I want to retrieve all products, and show which ones are already in category "X". My divs are like this:

   <span>Category "X"</span>
   [some php / fetch_assoc() / … ]
   <div class="product">Product A</div>
   <div class="product selected">Product B</div>
   <div class="product">Product B</div>

For now, it work with two queries: one to fetch all the products, and one to check if the product is in products_categories. So it's a lot of small queries with php inside the first one.

$getAllProducts = "SELECT products.name as productName, products.id as productID FROM products";
$resultProduct=$mysqli->query($getAllProducts);
while($product=$resultProduct->fetch_assoc()){
    $reqChecked = "SELECT * FROM products_categories
                   WHERE product_id=" .  $product["productID"] ."
                   AND category_id=" . $category["id"]; //$category["id"] is given
    $resultChecked = $mysqli->query($reqChecked);
    $row = $resultChecked->fetch_row();
    $selected = ""
    if ( isset($row[0]) ) {
        $selected = "selected";
    }

It is possible to do it with only one query? I tried with a left join (products_categories on products), but the products belonging to multiple categories are listed for every categories they're in.

EDIT

Here is some sample data

Product table

+----+-----------+
| id |   name    |
+----+-----------+
|  1 | product_1 |
|  2 | product_2 |
|  3 | product_3 |
+----+-----------+

category table

+----+------------+
| id |    name    |
+----+------------+
|  1 | category_1 |
|  2 | category_2 |
|  3 | category_3 |
+----+------------+

joining table

+------------+-------------+
| product_id | category_id |
+------------+-------------+
|          1 |           1 |
|          1 |           2 |
|          2 |           2 |
+------------+-------------+

Now, let's say I'm on a page editing category_2, and I want the following result:

+------------+--------------+-------------+
| product_id | product_name | category_id |
+------------+--------------+-------------+
|          1 | product_1    | 2           | --product_1 belongs to category_1 and category_2, but I only need it one time.
|          2 | product_2    | 2           |
|          3 | product_3    | NULL        | --product_3 belongs to nothing but I want to see it.
+------------+--------------+-------------+

Tim Biegeleisen Tim Biegeleisen · Accepted Answer · 2016-10-27T10:36:08

This is just a simple join problem. I originally thought you might need some query magic to show whether a product belongs to a given category. But if you just use the query below, you can check the category name for each row in your PHP and act accordingly.

SELECT p.id,
       p.name AS product,
       c.name AS category       -- check for value 'X' in your PHP code
FROM products p
INNER JOIN products_categories pc
    ON p.id = pc.product_id
INNER JOIN categories c
    ON c.id = pc.category_id

Note that your current approach is actually trying to do the join in the PHP code itself, which is undesirable for so many reasons.

Update:

SELECT t1.id AS product_id,
       t1.name AS product_name,
       CASE WHEN t2.productSum > 0 THEN '2' ELSE 'NA' END AS category_id
FROM products t1
LEFT JOIN
(
    SELECT product_id,
           SUM(CASE WHEN category_id = 2 THEN 1 ELSE 0 END) AS productSum
    FROM products_categories
    GROUP BY product_id
) t2
    ON t1.id = t2.product_id

Left join and belongs to

1 Answers