I am making a simple blog system where posts can have more than one category, so I have this query:
SELECT *, GROUP_CONCAT(item_category) AS item_categories
FROM (`dev_pages`)
LEFT JOIN `dev_items_to_categories` ON `dev_items_to_categories`.`item_id` = `dev_pages`.`page_id`
WHERE deleted_time IS NULL
AND `page_type` = 'blog'
AND `item_category` = '16'
ORDER BY `page_title` ASC
It works fine but if there are no results instead of coming back as nothing it returns NULL or the default value (See SQL Fiddle)
I managed to make a around but I was wondering if anyone had a better solution to this:
SELECT a.* FROM (
SELECT *, GROUP_CONCAT(item_category) AS item_categories
FROM (`dev_pages`)
LEFT JOIN `dev_items_to_categories` ON `dev_items_to_categories`.`item_id` = `dev_pages`.`page_id`
WHERE deleted_time IS NULL
AND `page_type` = 'blog'
AND `item_category` = '16'
ORDER BY `page_title` ASC
) AS a
WHERE page_id > 0
You need a GROUP BY clause:
SELECT *, GROUP_CONCAT(item_category) AS item_categories
FROM (`dev_pages`)
LEFT JOIN `dev_items_to_categories` ON `dev_items_to_categories`.`item_id` = `dev_pages`.`page_id` AND `item_category` = '16'
WHERE deleted_time IS NULL
AND `page_type` = 'blog'
GROUP BY dev_pages.page_id
ORDER BY `page_title` ASC
You also need to put item_category = '16' into the ON clause. Otherwise, you will filter out rows with no matches in dev_items_to_categories; since you're using LEFT JOIN rather than INNER JOIN, I assume you want those rows with null matches.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments