Having Trouble with AND OR WHERE Clause

I’ve written a query similar to this:

SELECT book_id, book_title, article_id, article_title
FROM website
LEFT OUTER JOIN book ON book.website_id = website.website_id
LEFT OUTER JOIN article ON article.website_id = website.website_id
WHERE website.website_id = 123
GROUP BY book_id, article_id
ORDER BY book_title, article_title

The query has left me with a result set similar to the one shown below. In this case, ‘SitePoint’ is the root of the query, and each column represents either a book or an article from SitePoint:

book_id   book_title                           article_id      article_title
21          Simply SQL                         19               jQuery Basics
21          Simply SQL                         39               Latest in HTML5
21          Simply SQL                         24               Rails Techniques
24          Ultimate HTML Reference            19               jQuery Basics
24          Ultimate HTML Reference            39               Latest in HTML5
24          Ultimate HTML Reference            24               Rails Techniques

My problem occurs when I then try to add a conditional WHERE clause to the query.

For instance, let’s say I want to show all books and articles with ‘SQL’ anywhere in the title:

WHERE book_title LIKE '%SQL%' AND article_title '%SQL%'

I want it to return Simply SQL, but since there is nothing in the ‘article_title’ column with ‘SQL’ in it, it wipes out the match from ‘book_title’ as well.

If I switch it to an ‘OR’ clause, it treats all three of the first rows as matches, which means the ‘Article’ table on my page continues to display items that don’t match the query.

Is there a better way to construct my query so that all of SitePoint’s books and articles are returned in the same result set, but without this overlap that messes up my WHERE clause?

Thanks in advance –

i think you want a union, not a double left join

but i can’t really tell because your fictitious examples are all messed up

please, how about describing your actual tables instead?

Thanks for your reply. You were correct that I needed to use UNION rather than join for my query. It seems obvious in retrospect.

My confusion was due in part to using the PHP ORM framework Doctrine, which doesn’t support UNIONs. The same query that “broke” when I added the WHERE clause had been working fine for non-conditional traversing of the entity object graph returned by Doctrine. But the SQL result set was fundamentally flawed as a result of joining dissimilar entities, as you suggested.

I’m a big fan of Simply SQL and reference it often in my work. Have you had a chance to check out ORM libraries such as Doctrine and Propel, and if so, do you have any thoughts on how well they hold up to your standards for database development?

sorry, i’m afraid i have no idea what ORM is

but thanks for the kind words about the book


ORM: “Object-Relational Mapping, the hard part of getting object-oriented languages and relational databases to work together.”

Silly pseudo-PHP example:

$id = 1; // id of an aquarium in the database
$aquarium = $ORM->getAquarium($id); // get the aquarium
$fish = $aquarium->getFish(); // get the fish in the aquarium

The ORM takes care of all the SQL stuff, so that the programmer only has to worry about programming the code.

Very powerful, but can also be limiting in some cases.

thanks, immerse, but that doesn’t help me, as i don’t do php either

so far i don’t see anything that can’t “hold up to” database development standards