Nested select command

[pentan]

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

[jworisek]

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

[code]SELECT DISTINCT(rRR) FROM RRInventory where rIndex='720';[/code]

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) [snapback]347697[/snapback][/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]

[fenway]

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 =.

[pentan]

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?

[fenway]

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?

[pentan]

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!

[fenway]

That would be why -- no subselects until 4.1+. In your case, you can get around it easier with server variables or in middleware.

[wickning1]

The join version should work fine on that older MySQL.

[code]SELECT DISTINCT r1.rRR FROM RRInventory r1, RRInventory r2 WHERE r1.rSite=r2.rSite AND r2.rIndex=720[/code]