pentan Posted February 20, 2006 Share Posted February 20, 2006 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 Link to comment https://forums.phpfreaks.com/topic/3491-nested-select-command/ Share on other sites More sharing options...
jworisek Posted February 20, 2006 Share Posted February 20, 2006 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] Quote Link to comment https://forums.phpfreaks.com/topic/3491-nested-select-command/#findComment-12014 Share on other sites More sharing options...
fenway Posted February 20, 2006 Share Posted February 20, 2006 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 =. Quote Link to comment https://forums.phpfreaks.com/topic/3491-nested-select-command/#findComment-12020 Share on other sites More sharing options...
pentan Posted February 20, 2006 Author Share Posted February 20, 2006 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? Quote Link to comment https://forums.phpfreaks.com/topic/3491-nested-select-command/#findComment-12024 Share on other sites More sharing options...
fenway Posted February 20, 2006 Share Posted February 20, 2006 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? Quote Link to comment https://forums.phpfreaks.com/topic/3491-nested-select-command/#findComment-12031 Share on other sites More sharing options...
pentan Posted February 20, 2006 Author Share Posted February 20, 2006 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 1mysql>I believe my versino is 4.0.22Thanks! Quote Link to comment https://forums.phpfreaks.com/topic/3491-nested-select-command/#findComment-12035 Share on other sites More sharing options...
fenway Posted February 20, 2006 Share Posted February 20, 2006 That would be why -- no subselects until 4.1+. In your case, you can get around it easier with server variables or in middleware. Quote Link to comment https://forums.phpfreaks.com/topic/3491-nested-select-command/#findComment-12037 Share on other sites More sharing options...
wickning1 Posted February 21, 2006 Share Posted February 21, 2006 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] Quote Link to comment https://forums.phpfreaks.com/topic/3491-nested-select-command/#findComment-12093 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.