This is where LEFT JOIN comes in: select folders.folder_name, folders.folder_id, count(folder_items.item_id) from (folders left join folder_items on folders.folder_id = folder_items.folder_id) where folders.folder_type='favourites' group by folder_name
Basically you need to decide which side you want to return all rows from. The left side (folders) or the right side (folder_items). If you wanted to return all rows from folder_items, you would use right join instead. This can produce some fun pecular results. :) Mike Eheler Chris Newland wrote: >Hi All, > >I'm trying to select a number of columns from 2 tables using an inner join. >One of the columns I'm selecting is a count and when the count is zero it doesn't return that row. >I'd like to return all rows, even if the count of one of the columns is zero. > >Can somebody show me how to fix my query? > >select >folders.folder_name, >folders.folder_id, >count(folder_items.item_id) >from (folders inner join folder_items on folders.folder_id = folder_items.folder_id) >where >folders.folder_type='favourites' >group by folder_name > >Thanks a lot, > >Regards, > >Chris > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
