[EMAIL PROTECTED] wrote:
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.
You have two options:
1) Join to table module_config twice, looking for one row in the first copy
and the second row in the second copy:
SELECT m.module_id, m.module_name
FROM module m
JOIN module_config mc1 ON m.module_id = mc1.module_id
JOIN module_config mc2 ON m.module_id = mc2.module_id
WHERE mc1.config_name = 'mapping' AND mc1.config_value = 'true'
AND mc2.config_name = 'loc_enabled' AND mc2.config_value = 'true'
2) Join to module_config once and look for either row. Each module_id will
have 0, 1, or 2 matching rows in module_config. You want the ones with 2
matching rows (met both requirements):
SELECT m.module_id, m.module_name
FROM module m
JOIN module_config mc ON m.module_id = mc.module_id
WHERE mc.config_value = 'true'
AND mc.config_name IN ('mapping', 'loc_enabled')
GROUP BY m.module_id
HAVING COUNT(*) = 2;
Try both ways to see which is faster for your data. If you ever need to
match more than 2 config_names, you'll definitely want option 2, as it will
be easier to write and faster.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
