MySQL select based on ENUM values

Question

I have a a suppliers directory table in a MySQL database which has an associated suppliers mappings table. This table maps the following criteria against an individual supplier:

1. Services.
2. Specialities
3. Standards

Just for background info each of these have values stored in 3 individual corresponding tables. All services that can be provided by any supplier are listed in the services table and the the mapping table captures the services which are offered by an individual supplier. A supplier can provide more than one service so they would have a mapping for each service they provide. Same goes for specialities and standards.

Where I am running into difficulties is on a search query where a user can query a list of suppliers on any one or all three of the above criteria. So for example they can search for a supplier with service a, speciality b and standard c. They can't search for multiple values on a service, speciality or standard

My mappings table looks like the following:

id int(11) unsigned NOT NULL 
supplier_id int(11) unsigned NOT NULL
entity_type enum('KEY_SERVICE','STANDARD','SPECIALITY') NOT NULL
entity_id int(11) NOT NULL 

Where entity_type maps is used to indicate the entity type being mapped and entity_id indicates the individual entity.

My query is as follows:

SELECT DISTINCT supplier_mappings.supplier_id, suppliers.company_name
FROM supplier_mappings
JOIN suppliers ON suppliers.id = supplier_mappings.supplier_id
WHERE (supplier_mappings.entity_type = 'KEY_SERVICE' AND supplier_mappings.entity_id =    '55')
AND (supplier_mappings.entity_type = 'SPECIALITY' AND supplier_mappings.entity_id = '218')
AND (supplier_mappings.entity_type = 'STANDARD' AND supplier_mappings.entity_id = '15'); 

which should return all suppliers who have a key service with the id 55, a speciality with the id 218 and a standard with the id 15. However it just returns an empty result set even though I know there is at least one supplier with these defined. It appears to be something to do with the compounded AND clauses but can't figure out what.

Would appreciate it if anyone has any ideas?

Solution 1

If you take a look at your query's conditions:

(supplier_mappings.entity_type = 'KEY_SERVICE' AND supplier_mappings.entity_id = '55') AND (supplier_mappings.entity_type = 'SPECIALITY' AND supplier_mappings.entity_id = '218') AND (supplier_mappings.entity_type = 'STANDARD' AND supplier_mappings.entity_id = '15')

For example: supplier_mappings.entity_type = 'KEY_SERVICE' AND supplier_mappings.entity_type = 'SPECIALITY' will return always FALSE value.

You must use a separate table foreach condition, like this

SELECT DISTINCT suppliers.ID, suppliers.company_name
           FROM suppliers,
                supplier_mappings mapA,
                supplier_mappings mapB,
                supplier_mappings mapC
          WHERE suppliers.ID = mapA.supplier_id
            AND suppliers.ID = mapB.supplier_id
            AND suppliers.ID = mapC.supplier_id
            AND mapA.entity_type = 'KEY_SERVICE'  AND mapA.entity_id = '55'
            AND mapB.entity_type = 'SPECIALITY'   AND mapB.entity_id = '218'
            AND mapC.entity_type = 'STANDARD'     AND mapC.entity_id = '15';

Solution 2

Take a look at your boolean logic. In the end you have an associative function ("AND")

so this

(supplier_mappings.entity_type = 'KEY_SERVICE' AND supplier_mappings.entity_id =    '55')
AND (supplier_mappings.entity_type = 'SPECIALITY' AND supplier_mappings.entity_id = '218')
AND (supplier_mappings.entity_type = 'STANDARD' AND supplier_mappings.entity_id = '15'); 

would return true only for something that has BOTH

supplier_mappings.entity_type = 'SPECIALITY'

and

supplier_mappings.entity_type = 'KEY_SERVICE' 

There is no way something can be these things at the same time. (this also holds true for the third value, and the id by the way). You don't want ALL these things at once. An example would be a simpeler case: If you want from a simple table both the rows that have id=3 and the rows that have id=4, your logic would NOT be WHERE id=3 AND id=4, because there are no rows that would have both. Instead, you'd ask for WHERE id=3 OR id=4

instead (I'm not sure this is completely fixing everything, but it should get you going), you want to do an OR for these separate cases:

(supplier_mappings.entity_type = 'KEY_SERVICE' AND supplier_mappings.entity_id =    '55')
OR (supplier_mappings.entity_type = 'SPECIALITY' AND supplier_mappings.entity_id = '218')
OR (supplier_mappings.entity_type = 'STANDARD' AND supplier_mappings.entity_id = '15');