Hi everyone,
I'm modifying my phpBB installation, and I need help with one of the queries I'm trying to modify. I'm a novice with SQL, so go easy, please!

I'm changing the viewforum.php file, which is the php file where the display of topics in a particular forum are displayed. Some of the topics in each of the forums have ratings, and some don't. I want to get the list of topics in a particular forum, and then sort by rating. Here's what I'm looking at so far:

Table 1: phpbb_topics
Relevant fields: forum_id, topic_id, topic_type

Table 2: phpbb_rate_results
Relevant fields: topic_id, rating

So, basically, this is what I've come up with:

SELECT t.*, r.rating
FROM phpbb_topics t, phpbb_rate_results r
WHERE t.forum_id = 23
AND t.topic_id = r.topic_id
ORDER BY t.topic_type DESC, r.rating DESC

The problem is, of course, that if a topic doesn't have a rating, it doesn't end up in the result set, which is NOT what I want. I want to have a list of all topics in forum 23, and to have that list sorted by rating, if there is one.

Can someone help?!

The answer is that I need to use a LEFT OUTER JOIN, such as the following:

SELECT t.*, r.rating
FROM phpbb_topics t LEFT OUTER JOIN phpbb_rate_results r ON t.topic_id = r.topic_id
WHERE t.forum_id = 23
ORDER BY t.topic_type DESC, r.rating DESC

Apparently, that's what LEFT OUTER JOINs are for -- where you dont want to limit the returned rows by the limits of the join criterion.

This query will return all posts in the phpbb_topics table with a forum_id =f 23, and then to sort the results by topic_type, and then by rating, if there is one for the topic. Exactly what I wanted!