need help with a query with multiple SELECT

[tjhilder]

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

[LazyJones]

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.

 

[ryanlwh]

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.

[tjhilder]

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?

[LazyJones]

[!--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.

 

[tjhilder]

[!--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).

 

 

[fenway]

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.

[tjhilder]

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.

[fenway]

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.

[tjhilder]

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

[fenway]

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.