Jump to content


Photo

just a quick join question


  • Please log in to reply
7 replies to this topic

#1 tjhilder

tjhilder
  • Members
  • PipPipPip
  • Advanced Member
  • 136 posts
  • LocationKent, UK

Posted 29 December 2005 - 08:51 PM

Hi,

i have this join..

$query = "SELECT news.*, COUNT(comment_id) AS count FROM news LEFT JOIN comments ON news.news_id=comments.news_id GROUP BY news_id DESC LIMIT $limitvalue, $limit";

at the mo, I have 2 tables, one called news, one called comments, both store the news_id, member_id and username, but instead of storing the username, I want to change it so that it only stores the member_id, so that if I have to change the username of a member, it will change the username on all the posts automaticly by the member_id.

I figured it was a case of adding another LEFT JOIN to this query, is this correct? maybe something like this.

$query = "SELECT news.*, COUNT(comment_id) AS count FROM news LEFT JOIN comments ON news.news_id=comments.news_id LEFT JOIN members.member_id=comments.member_id GROUP BY news_id DESC LIMIT $limitvalue, $limit";

I'm not sure if thats correct, if not maybe someone could help me out :) I'm not sure if it will work, maybe just for the comments, whereas I'd like to do it with the news table as well, not sure if possible.

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 30 December 2005 - 09:25 PM

You're missing the new table name, and the syntax is wrong -- you've only incuded the ON clause! Try the following (UNTESTED):

$query = "SELECT news.*, COUNT(comment_id) AS count FROM news LEFT JOIN comments ON news.news_id=comments.news_id LEFT JOIN members ON members.member_id=comments.member_id GROUP BY news_id DESC LIMIT $limitvalue, $limit";


Hope that helps.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 tjhilder

tjhilder
  • Members
  • PipPipPip
  • Advanced Member
  • 136 posts
  • LocationKent, UK

Posted 31 December 2005 - 08:30 AM

I just realised I mentioned that wrong, was meant to be joining members with news, not members with comments (although I do need that) but the query I gave needs members with news, I have another query for getting comments (should be able to work it all out now tho).

I do have one question though, would these 2 bits of code do different things or does it matter which way they are?

members.member_id=comments.member_id
comments.member_id=members.member_id

thanks :)



#4 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 31 December 2005 - 03:26 PM

I prefer to write the ON clause in the "order" of the JOIN just to make it clear which table will return nulls if it doesn't match, but that's just me.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#5 tjhilder

tjhilder
  • Members
  • PipPipPip
  • Advanced Member
  • 136 posts
  • LocationKent, UK

Posted 02 January 2006 - 12:44 PM

Ok kool, i'll have to try this out and see if I can get it working, if not i'll be right back here :P lol

thanks for the help.

#6 tjhilder

tjhilder
  • Members
  • PipPipPip
  • Advanced Member
  • 136 posts
  • LocationKent, UK

Posted 12 January 2006 - 07:56 AM

Ok run into a problem with my join.

[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--]$query = "[span style=\'color:blue;font-weight:bold\']SELECT news2.*, COUNT(comment_id) AS count FROM news2 LEFT JOIN members2 ON news2.member_id=members2.member_id LEFT JOIN comments2 ON news2.news_id=comments2.news_id WHERE news2.news_id={$_GET['[/span]id']} GROUP BY news2.news_id LIMIT 1"; [!--sql2--][/div][!--sql3--]

the count is fine, getting news from news2 isn't a problem, just can't seem to get the username from members2

what it basicly is doing is, counting the amount of comments from comments2 where news_id is XX (news2 and comments2 have a news_id)

it also needs to get the username from members2 where member_id is the same on news2 and members2, got it working with my comments, but can't seem to get it working with this one, not sure why.

my working comments query:[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--]$view_comments = "[span style=\'color:blue;font-weight:bold\']SELECT * FROM comments2 LEFT JOIN members2 ON members2.member_id=comments2.member_id WHERE news_id={$_GET['[/span]id']} GROUP BY comment_id [span style=\'color:green\']ORDER BY comment_id ASC"[/span]; [!--sql2--][/div][!--sql3--]

hope this all makes sense

MySQL version 4.1.14-standard

#7 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 12 January 2006 - 06:22 PM

I'm not sure I understand -- if you request member2.* in the SELECT statement, you're telling me that you won't get back the username?!?
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#8 tjhilder

tjhilder
  • Members
  • PipPipPip
  • Advanced Member
  • 136 posts
  • LocationKent, UK

Posted 13 January 2006 - 03:39 AM

Ok so I changed it from " SELECT news2.*, " to " SELECT news2.*, members2.*, " and it seems to have worked.

[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--]$query = "[span style=\'color:blue;font-weight:bold\']SELECT news2.*, members2.*, COUNT(comment_id) AS count FROM news2 LEFT JOIN members2 ON news2.member_id=members2.member_id LEFT JOIN comments2 ON news2.news_id=comments2.news_id WHERE news2.news_id={$_GET['[/span]id']} GROUP BY news2.news_id LIMIT 1"; [!--sql2--][/div][!--sql3--]

just gotta make sure it's working all over, if not i'll be back lol




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users