Jump to content


Photo

need help with a query with multiple SELECT


  • Please log in to reply
10 replies to this topic

#1 tjhilder

tjhilder
  • Members
  • PipPipPip
  • Advanced Member
  • 136 posts
  • LocationKent, UK

Posted 29 December 2005 - 03:13 PM

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

#2 LazyJones

LazyJones
  • Members
  • PipPipPip
  • Advanced Member
  • 78 posts

Posted 29 December 2005 - 03:50 PM

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.


#3 ryanlwh

ryanlwh
  • Staff Alumni
  • Advanced Member
  • 511 posts

Posted 29 December 2005 - 04:57 PM

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.
Please use EDIT * 100...
Please use
or [php] * 1000...

PLEASE READ THE POSTED SOLUTIONS CAREFULLY * 1000000...

#4 tjhilder

tjhilder
  • Members
  • PipPipPip
  • Advanced Member
  • 136 posts
  • LocationKent, UK

Posted 29 December 2005 - 06:49 PM

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?

#5 LazyJones

LazyJones
  • Members
  • PipPipPip
  • Advanced Member
  • 78 posts

Posted 29 December 2005 - 07:37 PM

[!--quoteo(post=331257:date=Dec 29 2005, 01:49 PM:name=tjhilder)--][div class=\'quotetop\']QUOTE(tjhilder @ Dec 29 2005, 01:49 PM) View Post[/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?
[/quote]

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.


#6 tjhilder

tjhilder
  • Members
  • PipPipPip
  • Advanced Member
  • 136 posts
  • LocationKent, UK

Posted 30 December 2005 - 09:22 AM

[!--quoteo(post=331276:date=Dec 29 2005, 07:37 PM:name=LazyJones)--][div class=\'quotetop\']QUOTE(LazyJones @ Dec 29 2005, 07:37 PM) View Post[/div][div class=\'quotemain\'][!--quotec--]
I would first get the member (with the last SELECT statement), then count the comments and messages.[/quote]

I tried that, no difference, still get
[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]Operand should contain 1 column(s).[/quote]



#7 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 30 December 2005 - 09:40 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#8 tjhilder

tjhilder
  • Members
  • PipPipPip
  • Advanced Member
  • 136 posts
  • LocationKent, UK

Posted 31 December 2005 - 10:16 AM

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.

#9 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 31 December 2005 - 04:59 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#10 tjhilder

tjhilder
  • Members
  • PipPipPip
  • Advanced Member
  • 136 posts
  • LocationKent, UK

Posted 31 December 2005 - 06:25 PM

[!--quoteo(post=331939:date=Dec 31 2005, 04:59 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Dec 31 2005, 04:59 PM) View Post[/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.
[/quote]
thanks, works a treat :D

#11 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 31 December 2005 - 06:38 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users