MySQL Join Question

How do you return records when joins are incomplete?

[Sekka]

Hi,

I have two tables, "Gallery" and "Entries". I am doing a query on "Gallery" to list all the galleries within the database (limited to 30 for pagination).

I want to join "Entries" onto "Gallery" so that I can use COUNT() to get how many entries are within each gallery.

Now, if a gallery has no entries, then the gallery is not returned in my current SQL statement because there is no join. Here is my SQL,

SELECT *, COUNT(entry_id) as theCount FROM Gallery
INNER JOIN Entries
ON Entries.gal_id = Gallery.gal_id
WHERE Gallery.deleted = 0
AND Entries.deleted = 0
GROUP BY Gallery.gal_id
LIMIT %s,%s
(%s are for the sprintf)

Now, what I want to know is how do I make my query return the record in "Gallery" even though there are no joined records in "Entries"? aka There is a 0 count for entries for a certain "gal_id".

Thank you.

[Sekka]

Nevermind. Figured it out myself with the MySQL documents.

I forgot LEFT JOIN makes a NULL record for the table if no join exists.

My SQL is now,

SELECT Gallery.*, COUNT(Entries.entry_id) as theCount FROM Gallery
LEFT JOIN Entries
ON Entries.gal_id = Gallery.gal_id
WHERE Gallery.deleted = 0
AND (Entries.deleted = 0 OR Entries.deleted IS NULL)
GROUP BY Gallery.gal_id
LIMIT %s,%s

[coopster]

Right on. An example of LEFT JOIN versus INNER JOIN [webmasterworld.com] for future readers.