Unanswered: multiple queries on joined table - need a better way
I have a problem which I guess must be quite common, however I cant find any decent solution.
I have 3 tables..
contact (title,firstname,surname etc)
custom_field (field_name) - used to allow any number of custom fields to be added to a contact.
custom_field_value (contact_id,custom_field_id,value) - joining table which specifies an actual value for a custom field in relation to a specific contact.
The complication comes when I want to filter customers based on a number of custom_field_value.value's.
Currently I am selecting contacts and adding a where clause for each custom field that does an inner select and returns contact ids for all contacts with values in the custom_field_value table matching the required criteria.
each custom field im filtering on then adds a inner select and also a join.. this is running very slow !
join the custom_field_value table to the contact table, use WHERE conditions to accept only those custom_field_value.value values that you're looking for, then do a GROUP BY on the contact columns, with a HAVING clause for COUNT(*) = N where N is the number of custom_field_value.value values searched for