SQL Query to fetch all product categories and whether the product is in it

Question

I have three tables. Product, product categories and product category links. The links table is there as a product can be in more than one category.

What I am trying to achieve is a list of ALL the categories with an additional field which states whether for that specific product id, if the product is in that category.

SELECT *, l.product_id as checked FROM `ProductCategories` LEFT JOIN ProductCategoriesLink l ON l.category_id = ProductCategories.id WHERE ( l.product_id = 1 ) AND ( ProductCategories.id > 0 ) GROUP BY ProductCategories.id ;

However at the moment, this only retrieves the categories which the product is in.

Any advice appreciated.

Answer 1

SELECT
    ProductCategories.*,
    l.product_id IS NOT NULL AS ProductInCategory
FROM
    ProductCategories
    LEFT JOIN ProductCategoriesLink AS l ON
        ProductCategories.id = l.category_id AND
        l.product_id = 1

This makes use of the fact that when a LEFT JOIN is performed, at least one row is returned for every row on the left-hand table. If there weren't matching rows on the right-hand table, the columns from that table are all NULL.