Complex Grouping query ? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Complex Grouping query ?

Author

Topic

jcdenmark
Starting Member

2 Posts

Posted - 2012-11-29 : 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

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-11-29 : 15:41:11

SELECT FolderID
from FoldersContacts
WHERE ContactID IN('A','B', 'C')
GROUP BY FolderID
HAVING COUNT(DISTINCT ContactID) = 3

Jim

Everyday I learn something that somebody else already knew

jcdenmark
Starting Member

2 Posts

Posted - 2012-11-29 : 16:10:01

Brilliant....thanks.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-11-29 : 17:32:45

You're welcome.

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources