[SOLVED] Is a subquery the only way?

[Philip]

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?

 

[fenway]

I'm assuing there's only one matching row in each subquery? If so, it's fine.

[Philip]

Yeah, there would only be one matching row.

 

It just seems like its doing a lot of work, but I guess it's okay.

[fenway]

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?

[Philip]

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

[fenway]

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.