Joining two tables in a query

Need the experts again....

[trillianjedi]

I'm building a custom RSS feed for a forum (phpBB2).

Basic forum query is quite easy:-

SELECT t.topic_id, t.topic_title FROM nuke_phpbb_topics AS t, nuke_phpbb_posts AS p, nuke_phpbb_forums AS f WHERE t.forum_id = f.forum_id AND f.auth_view = '0' AND p.topic_id = t.topic_id AND p.post_id = t.topic_last_post_id ORDER BY p.post_time DESC LIMIT 10

But I have a seperate RSS table containing the description that I need returned in the same query result set.

The RSS table containing the additional info I need also has the ID in each field, which corresponds to the t.topic_id in the nuke_phpbb table.

So the above query returns, eg:-

1211 My widgets just got shinier
1212 How to polish widgets

... and I need to take the descriptions from the RSS table which have the same topic ID and return as a part of that result to give something like:-

1211 My widgets just got shiner "TJ writes in Widgets General about his new polishing technique"

Where in the RSS table, the relevant row would have been:-

1211 "TJ writes in Widgets General about...."

Does that make sense?

In broekn English I would write the query like this:-

SELECT t.topic_id, t.topic_title FROM nuke_phpbb_topics AS t, nuke_phpbb_posts AS p, nuke_phpbb_forums AS f WHERE t.forum_id = f.forum_id AND f.auth_view = '0' AND p.topic_id = t.topic_id AND p.post_id = t.topic_last_post_id ORDER BY p.post_time and then add to each result the description from RSS WHERE RSS.ID = t.topic_ID DESC LIMIT 10

Do I need a join of some kind?

Thanks for any pointers,

TJ

[arran]

Assuming the description column in the RSD table is called 'description' then:

SELECT t.topic_id, t.topic_title, r.description FROM nuke_phpbb_topics AS t, nuke_phpbb_posts AS p, nuke_phpbb_forums AS f, RSS AS r WHERE t.forum_id = f.forum_id AND f.auth_view = '0' AND p.topic_id = t.topic_id AND p.post_id = t.topic_last_post_id AND r.ID = t.topic_ID ORDER BY p.post_time DESC LIMIT 10

[trillianjedi]

Arran - thanks you, works like a charm.

I also learned how to join data from two tables, even better ;-)

Many thanks,

TJ the SQL-newbie

[trillianjedi]

Hmmm..... got a bug now in my php - any ideas what's wrong with this?

The SQL query executes ok, but I get:-

<b>Fatal error</b>: Call to a member function on a non-object in <b>/home/<snip>/www/myrss.php</b> on line <b>73</b><br />

... as I try to output the dynamic part of the XML

Somewhere in here is where it goes wrong:-

for (;!$query->EOF; $query->MoveNext() ) 
{
$link = $query->fields[0]; 
$title = $query->fields[1];
$content = $query->fields[2];
$forum = $query->fields[3];
$poster = $query->fields[4];
echo "<item>\n";
echo "<title>$title</title>\n";
echo "<link>$link</link>\n";
echo "<description>$content posted by $poster in $forum</description>\n";
echo "</item>\n";
}

Basically I need to loop through the record set in $query and output the <item></item>'s.

Can anyone help?

Thanks,

TJ