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

Question

I have two tables for an online shop:

• one for categories: id, title
• one for products: id, ownerid, title, price (ownerid is the id of the parent category)

I want to select all the categories and also select the minimum and maximum price in each, hence the following 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

It works pretty much as expected with the only exception that if a category doesn't have any product in it, then it is not selected. While this seems logical since I ask for "s.ownerid=sc.id", I don't know enough SQL to make it work as intended. I need all the categories and for the ones that don't have products minp and maxp should be 0.

Any advice? Thanks.

Answer 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.

Answer 2

You want to make a left join:

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

When using a left join, you get the data from the table to the left of the left join (here categories) and to the right if exists.

Answer 3

I think what you want is LEFT JOIN, for example:

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

Answer 4

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

Answer 5

You should take a look at this http://phpweby.com/tutorials/mysql/32 basically you need to create a left or right join.

Answer 6

Try using LEFT JOIN:

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

However, using the cartesian product is not the best solution, in doubt. Most engines should be capable of optimizing it "away", but joins are also much more intuitive in most cases -- and faster, in doubt.