INNER JOIN query
| From: | Ron Piggott | 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