Mysql Query Confusion

Question

Hi,

I have these tables:

• domain
• domain_module
• domain_module_feature
• domain_module_feature_to_domain

A domain can have many domain_module_feature(s), the domain_module_feature_to_domain table contains 3 columns - dmf_id, dmf_domain_id and dmf_feature_id. For ease of this example lets assume domain has just 2 columns - dm_id and dm_name.

I want to allow an "advanced" search where i can see which domains make use of which features, for example has feature 1 and 2 but not feature 3. For some reason i cannot get my head around this at all.

i guess the pseudo-sql would be somethink like:

SELECT * FROM `domain` 
LEFT JOIN domain_module_feature_to_domain ON (`dm_id` = `dmf_domain_id`)
WHERE `dmf_feature_id` = '1' AND `dmf_feature_id` = '2' AND `dmf_feature_id` != '3'

Can you help?

Thanks.

Answer 1

Something along the lines of...

SELECT *
    FROM `domain`
    WHERE EXISTS (SELECT *
                      FROM domain_module_feature_to_domain
                      WHERE dm_id          = dmf_domain_id AND
                            dmf_feature_id = 1)                   AND
          EXISTS (SELECT *
                      FROM domain_module_feature_to_domain
                      WHERE dm_id          = dmf_domain_id AND
                            dmf_feature_id = 2)                   AND
          NOT EXISTS (SELECT *
                          FROM domain_module_feature_to_domain
                          WHERE dm_id          = dmf_domain_id AND
                                dmf_feature_id = 3);

Edit: Needs feature 1 AND 2, not 1 OR 2.