Hi all,

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 !

Is there a better way to do this ?

Thanks

yes

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