Jump to content


Photo

Nested select command


  • Please log in to reply
7 replies to this topic

#1 pentan

pentan
  • Members
  • PipPip
  • Member
  • 19 posts

Posted 20 February 2006 - 07:34 PM

Can anyone tell me what's wrong with this command? I keep getting an error messsage.

select distinct(rRR) from RRInventory where rSite=(select rSite from RRInventory where rIndex='720');

Both select statments work fine alone but together the die. I looked in the manual and I must be missing something but I can't see it.

Michael
Michael Shaffer
www.pelentan.com
"You know you have it, but can you _show_ you have it?"

#2 jworisek

jworisek
  • Members
  • PipPipPip
  • Advanced Member
  • 112 posts

Posted 20 February 2006 - 08:15 PM

maybe I'm missing something, but you make it seem like they are from the same table which may be your problem... I would change it to say

SELECT DISTINCT(rRR) FROM RRInventory where rIndex='720';

because whats the point in select rSite from the same table if you are never going to use it?

[!--quoteo(post=347697:date=Feb 20 2006, 01:34 PM:name=pentan)--][div class=\'quotetop\']QUOTE(pentan @ Feb 20 2006, 01:34 PM) View Post[/div][div class=\'quotemain\'][!--quotec--]
Can anyone tell me what's wrong with this command? I keep getting an error messsage.

select distinct(rRR) from RRInventory where rSite=(select rSite from RRInventory where rIndex='720');

Both select statments work fine alone but together the die. I looked in the manual and I must be missing something but I can't see it.

Michael
[/quote]

#3 fenway

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

Posted 20 February 2006 - 08:30 PM

Agreed -- however, I think the problem is that the inner query is returning more than one row, in which case you have to use IN instead of =.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#4 pentan

pentan
  • Members
  • PipPip
  • Member
  • 19 posts

Posted 20 February 2006 - 08:50 PM

It is from the same table. Is it going to die because of that? And I can't used the shortened version. The rIndex refers to a single line in the table. That line has a site field (rSite). I want to use that value to pull all the lines out of the table that have the same site value. Or am I missing a really easy way to do this?
Michael Shaffer
www.pelentan.com
"You know you have it, but can you _show_ you have it?"

#5 fenway

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

Posted 20 February 2006 - 09:15 PM

If that is case, you are correct -- you have to use a subselect (or a self-join). What is the error you're getting? What version of MySQL?
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#6 pentan

pentan
  • Members
  • PipPip
  • Member
  • 19 posts

Posted 20 February 2006 - 09:20 PM

The error I'm getting is:

mysql> select distinct(rRR) from RRInventory where rSite=(select rSite from RRInventory where rIndex='720');
ERROR 1064: 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 rSite from RRInventory where rIndex='720')' at line 1
mysql>


I believe my versino is 4.0.22

Thanks!
Michael Shaffer
www.pelentan.com
"You know you have it, but can you _show_ you have it?"

#7 fenway

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

Posted 20 February 2006 - 10:27 PM

That would be why -- no subselects until 4.1+. In your case, you can get around it easier with server variables or in middleware.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#8 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 21 February 2006 - 09:55 PM

The join version should work fine on that older MySQL.

SELECT DISTINCT r1.rRR FROM RRInventory r1, RRInventory r2 WHERE r1.rSite=r2.rSite AND r2.rIndex=720





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users