current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 4 down vote favorite

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?

share|improve this question
    
Can you create some sample data with your tables and put it into a SQL Fiddle? –  Kermit Jun 13 '13 at 13:02
    
See sqlfiddle.com/#!2/bddae/2. Hope that's right as haven't used SQL Fiddle before. If I search for a supplier with a key service = 1, speciality = 1 and a standard = 3 it should return back Supplier 1 and Supplier 4. –  Adrian Walls Jun 13 '13 at 14:00
add comment

2 Answers

up vote 1 down vote accepted

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';
share|improve this answer
    
Bingo. That's the ticket. Cheers. –  Adrian Walls Jun 13 '13 at 14:10
add comment
up vote 0 down vote

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');
share|improve this answer
    
Using OR will work but it means it will return a result set if any of those conditions match, I need to make sure that all the conditions match. –  Adrian Walls Jun 13 '13 at 14:03
    
that is not possible, you can't have something be 2 things at once –  Nanne Jun 13 '13 at 15:06
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.