Difficult And/Or criteria in SQL statement

[avarga82]

This is a little hard to explain, but here goes:

I have a table that holds contacts, a table that holds categories, and a many-to-many table that holds what contact belongs to what category. Each contact can belong to multiple categories, and each category can have multiple contacts.

Sample dataset
CONTACT_ID : CATEGORY_ID
1 : 2
1 : 3
1 : 4
2 : 3
2 : 4


Now, what I want to do is, get a recordset returned that shows contacts that have are a part of multiple categories. So, for example, I want to know all contacts that are in categories 1, 2, and 3.

Any ideas??

 

[amrita418]

Query below can be used to get all contacts that have multiple categories:

select 	distinct 
	a.contact_id
from 	#TableA a
where 	exists (select 	'x' 
		from 	#TableA b 
		where 	a.contact_id = b.contact_id 
			and a.category_id != b.category_id)

Next query lists all contacts that have categories 2,3 and 4 for them:

select 	a.contact_id
from 	#TableA a
where 	a.category_id = 2
	and exists (select 'x' 
		    from   #TableA b 
		    where  a.contact_id = b.contact_id 
			   and b.category_id = 3)
	and exists (select 'x' 
		    from    #TableA b 
		    where   a.contact_id = b.contact_id 
			    and b.category_id = 4)

Regards,
AA

 

[r937]

all contacts that are in categories 1,2,3 --

select contact_id
  from contacts
 where category_id in (1,2,3)
group
    by contact_id
having count(*) = 3

besides being simpler than the multiple subquery approach, it also allows you to do queries which would be next to impossible with subqueries, like "find all contacts who belong to at least three of the following five categories" -- change the IN list to list the 5 categories, and change the HAVING to >= 3



r937.com | rudy.ca

 

[avarga82]

These are EXACTLY what I needed. THANK YOU!!