Left Join WHERE clause help
Posted: Thu Jul 30, 2009 9:02 pm
I have the following query:
Which works perfectly. It selects all categories regardless of whether there are pages in the category or not (as long as the category is public and !deleted). However, it is also selecting all the pages that are NOT public and/or ARE deleted. Which I don't want. If I modify the query like so:
it will work again... except leaving out any category that doesn't have a page in it. Which is behavior I don't want.
Any hints?
Thanks.
Edit: Um... well, that was embarrassingly simple. Solution:Figures I would get the answer RIGHT after I posted. >.<
Code: Select all
$sql = "SELECT category.ctitle, page.id, page.title
FROM category LEFT JOIN page ON category.id = page.catid
WHERE category.id>1 AND category.public=1 AND category.deleted=0
ORDER BY category.ord, page.ord";Code: Select all
$sql = "SELECT category.ctitle, page.id, page.title
FROM category LEFT JOIN page ON category.id = page.catid
WHERE category.id>1 AND category.public=1 AND category.deleted=0
AND page.public=1 AND page.deleted=0
ORDER BY category.ord, page.ord";Any hints?
Thanks.
Edit: Um... well, that was embarrassingly simple. Solution:
Code: Select all
$sql = "SELECT category.ctitle, page.id, page.title
FROM category LEFT JOIN page
ON category.id = page.catid AND category.public = page.public AND category.deleted = page.deleted
WHERE category.id>1 AND category.public=1 AND category.deleted=0
ORDER BY category.ord, page.ord";