Announcement

Announcement Module
Collapse
No announcement yet.

Mysql inner join on 3 tables

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Mysql inner join on 3 tables

    Hi, i have 3 tables from which i want to extract values to third table.
    im doing this using perl since I've no idea how to join all these queries into one. i want to extract user information from these three tables. The problem im experiencing concerns lines: count(posts.id) and count(comments.id) -> i want to query for information how many comments every blog has received and how many posts author has written, but instead mysql gives me some more or less random numbers (these two count queries results are the same, in spite of they're obviously calculating something else). In the foreach loop i extract and compare dates of the earliest post or comment written by user in order to define user's "lifetime". Nevertheless i think it can be done in just one line.
    Do you have any idea how to do this?

    besides this code: I've created indexes on posts.blog_id and comments.blog_id. blogs.id is of course the primary key for 'blogs' table.

    my $blog_users = $dbh->selectall_arrayref("select blogs.red as red, blogs.id as id, blogs.blog_address as blog_address, blogs.name as name, count(posts.id) as postscount, count(comments.id) as comcount from blogs, posts, comments where blogs.id=posts.blog_id and blogs.id=comments.blog_id and comments.author_link is not null group by blogs.id", { Slice => {} } );foreach my $blog_user(@$blog_users){print "$blog_user->{id} ,";my $postscount = $blog_user->{postscount};my $comments_rec_counter = $blog_user->{comcount};my $comments_made_counter = $dbh->selectrow_array("select count(comments.id) from comments where comments.author_link=\'$blog_user->{blog_address}\'");my $postmin = $dbh -> selectrow_array ("select \@postmin:=min(date_sql) from posts where blog_link=\"$blog_user->{blog_address}\"");my $comm_min = $dbh -> selectrow_array ("select \@comm_min:=min(date_sql) from comments where author_link=\"$blog_user->{blog_address}\"");my $postmax = $dbh -> selectrow_array("select \@postmax:=max(date_sql) from posts where blog_link=\'$blog_user->{blog_address}\'");my $comm_max = $dbh -> selectrow_array("select \@comm_max:=max(date_sql) from comments where author_link=\"$blog_user->{blog_address}\"");
Working...
X