Forum Moderators: open

Message Too Old, No Replies

Optimizing an SQL query

         

supermanjnk

7:14 pm on May 4, 2007 (gmt 0)

10+ Year Member



SELECT contacts.* FROM groups
LEFT JOIN contacts_group on contacts_group.groupid = groups.groupid
INNER JOIN categories on categories.groupid = groups.groupid
AND contacts_cat.contactid in (SELECT contactid FROM contacts_cat WHERE categoryid='1' )
AND contacts_cat.contactid in (SELECT contactid FROM contacts_cat WHERE categoryid='2' )
INNER JOIN contacts_cat on contacts_cat.categoryid = categories.categoryid
INNER JOIN contacts on contacts.contactid = contacts_cat.contactid
WHERE (groups.groupid='1') group by contacts.contactid

This is my query. My problem is that while it works, it's not very effiecient. I only have two examples here, but right now for each category id, I will add another:
"AND contacts_cat.contactid in (SELECT contactid FROM contacts_cat WHERE categoryid='2' )"
to the statement.

Obviously this isn't very efficient.
Each category is it's own line in a database, then how it's assinged to a contact is also it's own line in another database, so And statements don't work because they aren't in the same row.

My tables are as follows
groups
---------------
groupid (int, auto, prim)
name

categories
---------------
categoryid (int, auto, prim)
groupid
name

contacts_group
---------------
groupid
contactid

contacts_cat
---------------
contactid
categoryid

contacts
---------------
contactid (int, auto, prim)
firstname
lastname
etc