SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Complex Grouping query ?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jcdenmark
Starting Member

2 Posts

Posted - 11/29/2012 :  15:07:53  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 11/29/2012 :  15:41:11  Show Profile  Reply with Quote
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
Go to Top of Page

jcdenmark
Starting Member

2 Posts

Posted - 11/29/2012 :  16:10:01  Show Profile  Reply with Quote
Brilliant....thanks.
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 11/29/2012 :  17:32:45  Show Profile  Reply with Quote
You're welcome.

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000