[EMAIL PROTECTED] wrote on 10/11/2005 01:27:12 PM:
>
>
> lets say i have two tables:
>
> module
> ---------
> VARCHAR module_name
> INTEGER module_id
>
> module_config
> ----------------
> INTEGER module_id
> VARCHAR config_name
> VARCHAR config_value
>
>
> config item names and values are rather arbitrary and depend on the
module.
>
> each module can have zero to many config items..
>
> How do i find out the id of a module with the following two
> module_config entries:
> config_name='mapping' and config_value='true'
> and
> config_name='loc_enabled' and config_value='true'
>
> it seems like i would have to use a subquery to check two rows at
> the same time. I wasn't really sure how to express it in SQL though.
>
No need for a subquery. There are at least two ways. The most direct uses
a self join but it does not scale well to more than two or three
conditions to match
SELECT a.module_id
FROM module_config a
INNER JOIN module_config b
on b.module_id = a.module_id
and b.config_name = 'loc_enabled'
and b.config_value = 'true'
WHERE a.config_name = 'mapping
and a.config_value='true';
The more flexible method is to use a COUNT() and a HAVING() to test for
how many conditions each module_id matches. It scales much better to more
than 2 or 3 conditions to match
SELECT module_id, count(1) as matches
FROM module_config
WHERE config_name IN ('loc_enabled', 'mapping')
AND config_value='true'
GROUP BY module_id
HAVING matches = 2;
Make sense? If you need to reuse the list of module_id's in another query,
just dump the results of this SELECT into a TEMPORARY TABLE and use them
from there.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
