INNER JOIN query

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


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: INNER JOIN query

Ron Piggott wrote:
> 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.

Use table aliases to show what's coming from where.

select
b.*,
blog_poster.username AS blog_poster,
blog_reply.username as blog_reply
from
blog b
inner join blog_owners blog_poster on
(b.blog_owners_reference=blog_poster.reference)
inner join blog_responses response on
(response.blog_reference=b.reference)
....

I couldn't work out the rest but hopefully you get the idea ;)

If you "alias" a tablename, then you can reference it twice in the same
query because you get "alias1" and "alias2" and you can easily see which
is which.

--
Postgresql & php tutorials
http://www.designmagick.com/


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php