| View previous topic :: View next topic |
| Author |
Message |
benny_01670
Joined: 11 Apr 2002
Posts: 22
|
 Posted: Tue Jan 07, 2003 12:34 pm Post subject: select within a select |
 |
|
I'm trying to use the following SQL query ... but cannot seem to get it to work.
| Code: | | SELECT id, title, post (SELECT count(Comment_id) FROM comments, blogs WHERE com_id = id) AS CommentCount FROM blogs DESC LIMIT 0, 7; |
But it dosen't seem to work with the SELECT count in it.
Any ideas how I could use the above query correctly?
Thanks in advance
Ben
_________________
Ben Swinney
fcuk-me.net | do ya wanna?
ben at fcuk-me dot net
http://www.fcuk-me.net / http://www.fcuk-me.com |
|
| Back to top |
|
 |
GamerZ
Joined: 15 May 2002
Posts: 536
Location: Singapore
|
| Posted: Tue Jan 07, 2003 4:29 pm Post subject: |
|
|
Wait can i know what are you tryign to do? ur query is miexed? 2 from 2 select?
_________________
++ GamerZ.Per.Sg - Complex Simplicity |
|
| Back to top |
|
|
benny_01670
Joined: 11 Apr 2002
Posts: 22
|
| Posted: Tue Jan 07, 2003 5:12 pm Post subject: |
|
|
I'm trying to select the number of comments replies to my blog.
This is the only way I can figure out how it may be done.
Hope this helps
Ben
_________________
Ben Swinney
fcuk-me.net | do ya wanna?
ben at fcuk-me dot net
http://www.fcuk-me.net / http://www.fcuk-me.com |
|
| Back to top |
|
|
cjc
Joined: 24 Dec 2002
Posts: 146
Location: New York
|
| Posted: Tue Jan 07, 2003 6:32 pm Post subject: |
|
|
| MySQL currently doesn't support subqueries (e.g., select within a select). |
|
| Back to top |
|
|
benny_01670
Joined: 11 Apr 2002
Posts: 22
|
| Posted: Tue Jan 07, 2003 10:53 pm Post subject: |
|
|
Can you think of another method for obtaing the same results?
Ben
_________________
Ben Swinney
fcuk-me.net | do ya wanna?
ben at fcuk-me dot net
http://www.fcuk-me.net / http://www.fcuk-me.com |
|
| Back to top |
|
|
cjc
Joined: 24 Dec 2002
Posts: 146
Location: New York
|
| Posted: Tue Jan 07, 2003 11:05 pm Post subject: |
|
|
| Use temporary tables, or use PHP arrays to store the results of your COUNT. |
|
| Back to top |
|
|
mikelittle
Joined: 11 May 2002
Posts: 374
Location: UK
|
| Posted: Tue Jan 07, 2003 11:42 pm Post subject: |
|
|
Benny,
do you want something like this?
| Code: |
SELECT ID, post_title, count(comment_ID) as comment_count
FROM b2posts LEFT JOIN b2comments ON ID = comment_post_id
GROUP BY ID
HAVING comment_count > 0
ORDER BY comment_count DESC LIMIT 7;
|
You may want to remove the having clause, if you wish to include posts without any comments.
Also I've sorted by number of comments I'm not sure that's what you wanted.
Hope this helps,
Mike
_________________
Mike Little
http://zed1.com/journalized/
"Share what you know. Learn what you don't." |
|
| Back to top |
|
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
