Returning exact set matches from link table

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 2000 Forums

SQL Server Development (2000)

Returning exact set matches from link table

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-06-27 : 09:18:08

Andy writes "Here is an example of a table I have:

EDP_Link Table
---------------

event_id
1
1
4
4
5
5

District_ID
7
1
3
8
7
3

Program_ID
1
5
4
3
1
4
--------------------------

I have a list of values passed from a form via checkboxes:

FORM.DistIDs = 7,3
FORM.ProgIDs = 1,4

I need to be able to get the event_id's that have ONLY BOTH 7 and 3 District_ID values and ONLY BOTH 1 and 4 Program_IDs (in the example data above, only event_id 5 would be a valid return value from the SQL code). If the event_id is associated with anything but these two other_id values, I do not want it returned. Events CAN be associated with as many District or Program IDs as necessary. There are two other tables, one called Districts and one called Programs.

I've messed with IN, ANY and ALL list search conditions to no avail. I've also come up with an almost working solution that uses COUNT(), but can't get it to return only the correct event_id if that event_id is associated with at least one of the items from the list.

For example, if event_id 750 is associated with 7 and 4, and event_id 789 is associated with 7 and 3, and I want to return only events with 7 and 3, I still get event_id 750 and 789 returned with the below query, since the NOT IN operator is allowing an event_id that is associated with 1 matching District_ID and the COUNT() function is seeing that there are only 2 rows.

MY CURRENT CODE (numbers are plugged in where variables would be):
-------------------------------------------------------------
SELECT DISTINCT event_id
FROM EDP_Link t1
WHERE District_ID NOT IN (SELECT District_ID FROM Districts WHERE District_ID NOT IN (7,3))
AND 2 = (SELECT COUNT(event_id)
FROM EDP_Link
WHERE event_id = t1.event_id)

-------------------------------------------------------------
In the above SQL, 2 would be the list length of the variable FORM.DistIDs, and 7,3 would be the actual comma delimited list of District_ID's (which is the value of FORM.DistIds).

Thanks for your time and I'd much appreciate the help,

Andy"

Kevin Snow
Posting Yak Master

149 Posts

Posted - 2002-06-27 : 12:31:26

quote:

SELECT DISTINCT event_id
FROM EDP_Link t1
WHERE District_ID NOT IN (SELECT District_ID FROM Districts WHERE District_ID NOT IN (7,3))
AND 2 = (SELECT COUNT(event_id)
FROM EDP_Link
WHERE event_id = t1.event_id)

It's not too pretty, but if you join the table to itself based on event_id, you should get all combinations of record pairs, at least one of which will match your criteria.

With an involved set of OR statements, you can insure that the paired result set will include districts 7 and 3 AND will include programs 1 and 4

Select Distinct Event_ID
from EDP_Link t1 ,EDP_Link t2
where t1.event_ID = t2.event_ID and
((t1.district_id =7 and t2.district_id=3) OR(t1.district_id =3 and t2.district_id=7)) and
((t1.program_id =1 and t2.program_id=4) OR(t1.program_id =4 and t2.program_id=1))

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources