How do I join tables without using INNER JOIN?

Question

I'm no good at writing MySQL queries, so this query isn't working as it should. I want to select all "group"s, and fetch external information on the groups by IDs saved in group, like the group's creator, last person to update it and the number of images in the group.

In this query I'm using INNER JOIN, but apparently groups that have no images arent selected in this query. I want the query to select ALL groups no matter what, and fetch information on all the groups using the IDs. If a group has no images, i want the image count to be 0, not the group to be ignored.

This is the current query:

SELECT g.id, g.name, g.date_created, g.date_updated, g.created_by, 
    c.fullname AS creator_name, g.updated_by, u.fullname AS updater_name, COUNT(i.id) as image_count

FROM    gallery_groups g INNER JOIN 
        users c INNER JOIN 
        users u INNER JOIN 
        gallery_images i

WHERE g.created_by=c.id AND g.updated_by=u.id AND i.group=g.id $id
GROUP BY g.name
ORDER BY g.date_updated DESC, g.name

I have tried replacing INNER JOIN with LEFT JOIN, RIGHT JOIN and OUTER JOIN, but all just result in sql errors.

Thanks for any and all help!

Answer 1

Put the join criteria with the joins where they belong. Don't mix aggregates (sum/max etc on columns) and non-aggregates (fields not in the GROUP BY and not in a function) even if MySQL allows you to

SELECT g.id, g.name, g.date_created, g.date_updated, g.created_by, 
    c.fullname AS creator_name, g.updated_by, u.fullname AS updater_name, COUNT(i.id) as image_count
FROM    gallery_groups g LEFT JOIN 
        users c ON g.created_by=c.id LEFT JOIN 
        users u ON g.updated_by=u.id LEFT JOIN
        gallery_images i ON i.group=g.id
WHERE g.id = $id
GROUP BY g.id, g.name, g.date_created, g.date_updated, g.created_by, 
    c.fullname, g.updated_by, u.fullname
ORDER BY g.date_updated DESC, g.name

Since you are grouping by pretty much everything else except the image count, it may be better to just subquery that

SELECT g.id, g.name, g.date_created, g.date_updated, g.created_by, 
    c.fullname AS creator_name, g.updated_by, u.fullname AS updater_name,
    IFNULL((
        select COUNT(1)
        from gallery_images i
        WHERE i.group=g.id), 0) as image_count
FROM    gallery_groups g LEFT JOIN 
        users c ON g.created_by=c.id LEFT JOIN 
        users u ON g.updated_by=u.id LEFT JOIN
        gallery_images i ON i.group=g.id
WHERE g.id = $id
ORDER BY g.date_updated DESC, g.name

Answer 2

When you join two tables in SQL you have give criteria to join on.

SELECT g.id, g.name, g.date_created, g.date_updated, g.created_by, 
c.fullname AS creator_name, g.updated_by, u.fullname AS updater_name, COUNT(i.id) as image_count
FROM    gallery_groups g 
    INNER JOIN users c ON g.created_by=c.id
    INNER JOIN users u ON g.updated_by=u.id
    INNER JOIN gallery_images i ON i.group=g.id
GROUP BY g.name
ORDER BY g.date_updated DESC, g.name

The FROM clause is executed first, and gathers all the data to be returned by the query. Then the GROUP BY and WHERE clauses are executed which reduce the number of rows that will be returned. Finally the ORDER BY clause is executed which rearranges the whole result set.

The difference between inner and outer joins is the behavior when mismatches happen. Inner joins drop rows that are mismatched, outer joins allow mismatches. You almost always want an inner join unless you have a reason why you need an outer join.