|
jcdenmark
Starting Member
2 Posts |
 Posted - 11/29/2012 : 15:07:53
|
To anyone who might help...
I have a conventional junction table, e.g. for Contacts associated with a folder, a FoldersContacts table, including basically the FolderID, the ContactID and a ContactCategory.
- Querying for folders associated with 1 specific Contact (Category irrelevant) is trivial;
- Querying for folders associated with BOTH of 2 ContactIDs, regardless of category, can be done by the below, I think (but maybe prone to error below?)...
e.g. SELECT FolderID from FoldersContacts WHERE ContactID IN('A','B')
GROUP BY FolderID
HAVING COUNT(FolderID)>=2;
Querying for folders with ALL OF 3 different contacts;
The following does not work:
SELECT FolderID from FoldersContacts WHERE ContactID IN('A','B', 'C')
GROUP BY FolderID HAVING COUNT(FolderID)>=3
....because there are many cases where the COUNT(FolderID)>=3, but only 1 or 2 of 'A','B','C' are listed in the FoldersContacts table, albeit with different ContactCategories.
Question: how do I efficiently query for all folders having all of 3 (or more) Contacts associated with them ?
I have tried using a sub-query to retrieve all the contacts for a particular FolderID, and then establishing whether the (DISTINCT) Contacts Listed completely match a temp Contact table with 'A', 'B', 'C'; I did get this working:
SELECT DISTINCT FolderID
FROM FoldersContacts AS FTOP
WHERE
NOT EXISTS
(SELECT DISTINCT ContactID FROM 3Contacts LEFT JOIN
(SELECT ContactID FROM FoldersContacts WHERE
FolderID=TTMAIN.FolderID) AS SQ
ON 3Contacts.ContactID=SQ.ContactID
WHERE SQ.ContactID IS NULL
)
This works in test, but on a larger production table (e.g. FoldersContacts with 23000 records), it is UNBELIEVABLY SLOW.
Any ideas on speeding this up ? I'm sure I'm missing something basic.
And then once I have all the folders having Contacts 'A','B','C', can I then use a similar routine to further limit by Contact Category ?
Any help massively appreciated...I been really struggling with this...
Jim |
|
Complex Grouping query ?
