Patrick, You need "outer joins" to do this. Try searching for "outer join sql tutorial" on Google.
Hope this helps, Andy > -----Original Message----- > From: Patrick Crowley [mailto:[EMAIL PROTECTED] > Sent: 07 August 2003 16:51 > To: [EMAIL PROTECTED] > Subject: STUMPED: How Can I Pull Related Info Using Subqueries/Joins? > > > 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/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
