Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2006
    Posts
    1

    Unanswered: multiple queries on joined table - need a better way

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by tim.pickup
    Is there a better way to do this ?
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •