INNER JOIN query

From: Date: Wed, 05 Nov 2008 02:25:12 +0000
Subject: INNER JOIN query
Groups: php.db 
Request: Send a blank email to php-db+get-45593@lists.php.net to get a copy of this message
I am writing a blog application. Table blog has the blog entries Table blog_owners is the user profiles Table blog_responses is responses to the blog entries I am writing the module where the user approves or deletes user comments when the blog entry is in 'moderated' mode. I am trying to find out if I am retrieve 2 user profiles from blog_owners with just one query --- the person who posted the blog entry and the person who responded to it. "INNER JOIN blog_owners on blog_owners.reference = blog_responses.comments_blog_owners_reference" makes the query not work, because I have already INNER JOIN blog_owners . But this time I am trying to retrieve the blog comment creators profile. The blog_responses table has a column comments_blog_owners_reference which is the reference (auto_increment) value in the blog_owners table. How can I do this in just 1 query / do I need to query the database a second time? SELECT * FROM ( ( blog INNER JOIN blog_owners on blog.blog_owners_reference = blog_owners.reference ) INNER JOIN blog_responses on blog_responses.blog_reference = blog.reference ) INNER JOIN blog_owners on blog_owners.reference = blog_responses.comments_blog_owners_reference WHERE blog.blog_owners_reference =$user_reference AND blog_responses.approved_for_display =0 ORDER BY blog_responses.date ASC, blog_responses.blog_reference ASC LIMIT 1

« previous php.db (#45593) next »