Page 1 of 1

Left Join WHERE clause help

Posted: Thu Jul 30, 2009 9:02 pm
by Chalks
I have the following query:

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";
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:

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";
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:

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";
Figures I would get the answer RIGHT after I posted. >.<