Left Join WHERE clause help

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Chalks
Forum Contributor
Posts: 447
Joined: Thu Jul 12, 2007 7:55 am
Location: Indiana

Left Join WHERE clause help

Post 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. >.<
Post Reply