Philip Posted January 13, 2009 Share Posted January 13, 2009 Okay guys, I can do it this query using subqueries - but I'm wondering if there is a more efficient way to do this: Settings table: VariableValue Setting11 Setting2999 Members Table: IDNameCode1Code2 1Phil751000 2Bob065 3Joe53210 Let's say I want to: Select members.id, members.name from members, settings where members.code1 > settings.setting1 and members.code2 < settings.setting Right now, above query is obviously invalid, since the field name is "Variable" and "Value" (and not "Setting1"/"Setting2"), so following works: SELECT `id`, `name` FROM `members` WHERE code1 > (SELECT `value` FROM `settings` WHERE `variable`='Setting1') AND code2 < (SELECT `value` FROM `settings` WHERE `variable`='Setting2') Which would return ID 3 - Joe. Is there any way to simplify or make the above query more efficient. The real query I want to do would have 4+ sub queries, which I know probably wouldn't be too ideal, performance wise. Or is it okay to multiple subqueries? Link to comment https://forums.phpfreaks.com/topic/140632-solved-is-a-subquery-the-only-way/ Share on other sites More sharing options...
fenway Posted January 13, 2009 Share Posted January 13, 2009 I'm assuing there's only one matching row in each subquery? If so, it's fine. Link to comment https://forums.phpfreaks.com/topic/140632-solved-is-a-subquery-the-only-way/#findComment-736033 Share on other sites More sharing options...
Philip Posted January 13, 2009 Author Share Posted January 13, 2009 Yeah, there would only be one matching row. It just seems like its doing a lot of work, but I guess it's okay. Link to comment https://forums.phpfreaks.com/topic/140632-solved-is-a-subquery-the-only-way/#findComment-736355 Share on other sites More sharing options...
fenway Posted January 14, 2009 Share Posted January 14, 2009 Yeah, there would only be one matching row. Then I like to use "LIMIT 1" to make it obvious. It just seems like its doing a lot of work, but I guess it's okay. Not sure how you mean... what other way would there be? Link to comment https://forums.phpfreaks.com/topic/140632-solved-is-a-subquery-the-only-way/#findComment-736882 Share on other sites More sharing options...
Philip Posted January 14, 2009 Author Share Posted January 14, 2009 I dunno, I was hoping there was another way than to do a subquery on each value I needed to check - but I guess not. I just see a subquery as being a resource hog for some reason, even though it's probably not Link to comment https://forums.phpfreaks.com/topic/140632-solved-is-a-subquery-the-only-way/#findComment-736926 Share on other sites More sharing options...
fenway Posted January 14, 2009 Share Posted January 14, 2009 I can be, but if you're just going to get back a single value, then it doesn't really matter if you do it in a separate query or not. Link to comment https://forums.phpfreaks.com/topic/140632-solved-is-a-subquery-the-only-way/#findComment-737028 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.