Jump to content

Archived

This topic is now archived and is closed to further replies.

pentan

Nested select command

Recommended Posts

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

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
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 =.

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
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!

Share this post


Link to post
Share on other sites
That would be why -- no subselects until 4.1+. In your case, you can get around it easier with server variables or in middleware.

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites

×

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.