Jump to content

need help with a query with multiple SELECT


tjhilder

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 :)

Link to comment
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.

 

Link to comment
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?

Link to comment
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.

 

Link to comment
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).

 

 

Link to comment
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.

Link to comment
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.

Link to comment
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.

Link to comment
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

Link to comment
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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.