How to select from two tables in MySQL even if not all rows in one table have corespondents in the other?

Solution 1:

To do this you need an outer join. By the way, the way you are writing your query with an implicit join is outdated and no longer recommended. Using the JOIN keyword is recommended. This also makes it easier to change an inner join to an outer join.

FROM categories AS sc
LEFT JOIN products AS s
ON s.ownerid=sc.id

To return 0 instead of NULL use IFNULL(..., 0). The entire query becomes:

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

You may also want to consider if it would be better to return the default NULL instead of 0 for categories that have no products.

Solution 2:

How about a LEFT JOIN (W3Schools)?

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