just a quick join question

[tjhilder]

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.

[fenway]

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.

[tjhilder]

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

 

 

[fenway]

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.

[tjhilder]

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 lol

 

thanks for the help.

[tjhilder]

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

[fenway]

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?!?

[tjhilder]

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