STUMPED: How Can I Pull Related Info Using Subqueries/Joins?

[Patrick Crowley]

I'm creating a tool to browse a database of movie listings. The browser
pulls up 25 results at a time, and you can page through them using 'Next'
and 'Prev' tools. Pretty basic stuff.

Here are my tables:
movies
directors
comments
movies_directors
movies_comments
etc...
(primary key is movies.id)

But here's where I'm stuck: for each film, I need to pull the movie info in
'movies', plus any related data from other tables, like this:

The Lord of the Rings | Peter Jackson | 3 comments
Episode II | George Lucas | 0 comments
Indiana Jones | Steven Spielberg | 15 comments

I seem to run into problems when I try to join info from all of these
related tables. If there's a match, great. But, if not (like a movie with no
comments), the movie is excluded from the result set.

I've tried all sorts of SQL queries to make this work, but nothing seems to
do the trick.

SO, HERE'S MY QUESTION, IS THERE ANY WAY TO DO THIS QUERY WITHOUT
SUBQUERIES/MYSQL 4.0? Or would the best approach be to use PHP to do all the
subquery lookups?

Thanks for your help!
Patrick
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/my***********...ie.nctu.edu.tw