How Can I Retrieve All Categories and Their Minimum/Maximum Product Prices, Including Categories Without Products, in MySQL?

Selecting from Multiple Tables with Missing Correspondence in MySQL

To effectively query across multiple tables, even in scenarios where not all rows in one table have corresponding entries in another, MySQL provides the concept of outer joins. Consider the scenario of an online shop with categories and products tables.

Suppose we want to retrieve all categories and calculate minimum and maximum prices for each. However, we want to include categories without any products and set their min and max prices to 0. The original query:

SELECT
sc.*, MIN(s.price) AS minp, MAX(s.price) AS maxp
FROM categories AS sc, products AS s
WHERE s.ownerid=sc.id
GROUP BY sc.id

Excludes categories without products due to the inner join condition. To include all categories, we use a LEFT JOIN:

SELECT
    sc.*,
    IFNULL(MIN(s.price), 0) AS minp,
    IFNULL(MAX(s.price), 0) AS maxp
FROM categories AS sc
LEFT JOIN products AS s
ON s.ownerid = sc.id
GROUP BY sc.id

The LEFT JOIN preserves all rows in the categories (sc) table, allowing the query to retrieve all categories, including those without products. The IFNULL function sets null values (which occur when there are no corresponding products) to 0, ensuring that minp and maxp are always returned with a valid value.

This approach ensures that the query retrieves all categories, regardless of whether they have corresponding products, and it correctly assigns 0 for missing price information.

The above is the detailed content of How Can I Retrieve All Categories and Their Minimum/Maximum Product Prices, Including Categories Without Products, in MySQL?. For more information, please follow other related articles on the PHP Chinese website!