Jump to content

Archived

This topic is now archived and is closed to further replies.

tjhilder

need help with a query with multiple SELECT

Recommended Posts

Hi,

 

whats wrong with the way I have this written? I need some help with it.

 

$get_member = "SELECT (SELECT COUNT(comment_id) FROM comments WHERE member_id='{$_SESSION['member_id']}') AS comment_count, (SELECT COUNT(member_id) FROM messages WHERE member_id='{$_SESSION['member_id']}') AS message_count, SELECT * FROM members WHERE member_id='{$_SESSION['member_id']}'";

 

I get this error.

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT * FROM members WHERE member_id='2'' at line 1

 

any help would be great :)

Share this post


Link to post
Share on other sites

Wow, your query is really messed up.

SELECT (SELECT COUNT(comment_id) FROM comments WHERE member_id='{$_SESSION['member_id']}')

should probably be something like

SELECT COUNT(SELECT comment_id FROM comments WHERE member_id='{$_SESSION['member_id']}')

 

And the "base" query doesn't make much sense, for example, there's no FROM statement.

 

Share this post


Link to post
Share on other sites

he's trying to conbime three select statements into one so he can use one result set, although that's not really necessary. The last SELECT statement needs to be surrounded with a parenthesis.

Share this post


Link to post
Share on other sites

I tried doing it with the last one in ( ) 's but it gives me this error:

Operand should contain 1 column(s).

 

the query ( with ( )'s )

$get_member = "SELECT (SELECT COUNT(comment_id) FROM comments WHERE member_id='{$_SESSION['member_id']}') AS comment_count, (SELECT COUNT(member_id) FROM messages WHERE member_id='{$_SESSION['member_id']}') AS message_count, (SELECT * FROM members WHERE member_id='{$_SESSION['member_id']}')";

 

any suggestions?

Share this post


Link to post
Share on other sites

[!--quoteo(post=331257:date=Dec 29 2005, 01:49 PM:name=tjhilder)--][div class=\'quotetop\']QUOTE(tjhilder @ Dec 29 2005, 01:49 PM) 331257[/snapback][/div][div class=\'quotemain\'][!--quotec--]

I tried doing it with the last one in ( ) 's but it gives me this error:

Operand should contain 1 column(s).

 

the query ( with ( )'s )

$get_member = "SELECT (SELECT COUNT(comment_id) FROM comments WHERE member_id='{$_SESSION['member_id']}') AS comment_count, (SELECT COUNT(member_id) FROM messages WHERE member_id='{$_SESSION['member_id']}') AS message_count, (SELECT * FROM members WHERE member_id='{$_SESSION['member_id']}')";

 

any suggestions?

 

You didn't make the corrections i suggested. But I really doubt it would work anyway...

I would first get the member (with the last SELECT statement), then count the comments and messages.

 

That thing just looks so horrible.

 

Share this post


Link to post
Share on other sites

[!--quoteo(post=331276:date=Dec 29 2005, 07:37 PM:name=LazyJones)--][div class=\'quotetop\']QUOTE(LazyJones @ Dec 29 2005, 07:37 PM) 331276[/snapback][/div][div class=\'quotemain\'][!--quotec--]

I would first get the member (with the last SELECT statement), then count the comments and messages.

 

I tried that, no difference, still get

[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]Operand should contain 1 column(s).

 

 

Share this post


Link to post
Share on other sites

You're getting that error because you can't return "SELECT *" in a subquery, AFAIK.

 

But I agree with LazyJones -- the cleaner way to write your query would be the following (UNTESTED):

 

$get_member = "SELECT * FROM members WHERE member_id='{$_SESSION['member_id']}', (SELECT COUNT(comment_id) FROM comments WHERE member_id='{$_SESSION['member_id']}') AS comment_count, (SELECT COUNT(member_id) FROM messages WHERE member_id='{$_SESSION['member_id']}') AS message_count";

 

Hope that helps.

Share this post


Link to post
Share on other sites

that query gets this error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' (SELECT COUNT(comment_id) FROM comments WHERE member_id='2') AS comment_count, ' at line 1

then i thought about what you said "You're getting that error because you can't return "SELECT *" in a subquery" and changed the query to:

$get_member = "SELECT (SELECT username FROM members WHERE member_id='{$_SESSION['member_id']}'), (SELECT COUNT(comment_id) FROM comments WHERE member_id='{$_SESSION['member_id']}') AS comment_count, (SELECT COUNT(member_id) FROM messages WHERE member_id='{$_SESSION['member_id']}') AS message_count";

which worked, but wouldn't show up the username, so I changed it:

(SELECT username FROM members WHERE member_id='{$_SESSION['member_id']}') AS username,

and then the username showed up and the query worked. if it hadn't I would just have used 2 queries. but with 1 theres less code.

Share this post


Link to post
Share on other sites

Sorry, I wasn't thinking straight... you have to include the subqueries in the column list part of the outer query, not after it:

 

$get_member = "SELECT *, (SELECT COUNT(comment_id) FROM comments WHERE member_id='{$_SESSION['member_id']}') AS comment_count, (SELECT COUNT(member_id) FROM messages WHERE member_id='{$_SESSION['member_id']}') AS message_count FROM members WHERE member_id='{$_SESSION['member_id']}'";

 

Don't know what I was thinking -- sorry about that.

Share this post


Link to post
Share on other sites

[!--quoteo(post=331939:date=Dec 31 2005, 04:59 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Dec 31 2005, 04:59 PM) 331939[/snapback][/div][div class=\'quotemain\'][!--quotec--]

Sorry, I wasn't thinking straight... you have to include the subqueries in the column list part of the outer query, not after it:

 

$get_member = "SELECT *, (SELECT COUNT(comment_id) FROM comments WHERE member_id='{$_SESSION['member_id']}') AS comment_count, (SELECT COUNT(member_id) FROM messages WHERE member_id='{$_SESSION['member_id']}') AS message_count FROM members WHERE member_id='{$_SESSION['member_id']}'";

 

Don't know what I was thinking -- sorry about that.

thanks, works a treat :D

Share this post


Link to post
Share on other sites

Glad you got it working. Yeah, if you put it after the FROM keyword, the parser thinks you're trying to use a subquery as one of the tables, and wants an alias for it, and so on. Useful for some things, but not in this case. Sorry for the confusion -- upon closer inspection, I even had those subqueries as part of the where clause, not in the FROM... my bad.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.