[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]

Reply via email to