Returning ONLY exact matches from a link table 1

[SidianConsulting]

Hi everyone:

Here is an example of a table I have:

event_id other_id
1 45
1 59
1 75
1 14
4 14
4 12
4 45
5 14
5 45

--------------------------

Say I have a list of values passed in a FORM variable via checkboxes:

45,14

I need to be able to get the event_id's that have ONLY BOTH 45 and 14 other_id values (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.

I've messed with IN, NOT IN, ANY, ALL, and EXISTS list search conditions to no avail.

Thanks,

Tek

 

[forecasting]

You could try this.

SELECT myTbl.event_id
FROM myTbl INNER JOIN
(SELECT event_id
FROM myTbl
GROUP BY event_id
HAVING COUNT(event_id) = 2) b
ON myTbl.event_id = b.event_id
WHERE (myTbl.other_id = 45) OR (myTbl.other_id = 14)
GROUP BY myTbl.event_id
HAVING (COUNT(myTbl.event_id) = 2)

 

[SidianConsulting]

Forecasting,

Thanks for the reply.

Unfortunately, this does not solve my problem. I need to be able to specify a list of values, which are dynamic based on which checkboxes the user submitted, so checking static values with OR won't work. I just used those values as an example.

Can anyone else help?

This is what I have so far, and I'm so close, but no dice:

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

This is my final SQL code, except 2 variables are omitted.

Where you see: 2 = (SELECT...)

The 2 would be the list length of the District_IDs variable passed to the SQL query.

Where you see: District_ID NOT IN(4,7)

The 4,7 is the actual list passed to the SQL query as
a variable.

Anyone? This has been wracking my brain for days. This code returns any two events that are associated with District_IDs 4 and 7 only, but there is a catch:

If a District_ID has either 4 OR 7 as it's value, it will be returned AS LONG AS there are only 2 returned rows. What this means is, if another Event_ID has a 4 and 8 associated with it, it will also be returned since the matching pattern I am using seems to be using OR and not AND.

Does anyone know what I am talking about? I hope so!

Thanks,

Tek

 

[crystalized]

I know in relational calculus there is Division which I believe satisfies this problem, but I can not find anything in SQL server that matches that capability.

One of my co-workers found this little gem on the topic that should hopefully help you out.

http://groups.google.ca/groups?q=di...204261017.305cb840@posting.google.com&rnum=30

Good Luck Crystal
crystalized_s@yahoo.com

--------------------------------------------------

Experience is one thing you can't get for nothing.

-Oscar Wilde

 

[forecasting]

SELECT myTbl.event_id
FROM myTbl INNER JOIN
(SELECT event_id AS e_id
FROM myTbl
GROUP BY event_id
HAVING COUNT(event_id) = 2) b ON myTbl.event_id = b.e_id
WHERE (myTbl.other_id IN (14, 45))
GROUP BY myTbl.event_id
HAVING (COUNT(myTbl.event_id) = 2)

The 2 would be the list length of the District_IDs variable passed to the SQL query.

Where you see: District_ID NOT IN(14,45)

The 14,45 is the actual list passed to the SQL query as
a variable.

 

[forecasting]

SELECT myTbl.event_id
FROM myTbl INNER JOIN
(SELECT event_id AS e_id
FROM myTbl
GROUP BY event_id
HAVING COUNT(event_id) = 2) b ON myTbl.event_id = b.e_id
WHERE (myTbl.other_id IN (14, 45))
GROUP BY myTbl.event_id
HAVING (COUNT(myTbl.event_id) = 2)

The 2 would be the list length of the District_IDs variable passed to the SQL query.

Where you see: myTbl.other_id IN(14,45)

The 14,45 is the actual list passed to the SQL query as
a variable.

 

[SidianConsulting]

Forecasting,

Thanks, that works great! I haven't been able to break it yet

Crystal,

That link on google.ca groups is interesting. I'm going to check out writing the query using the relational division method to see how fast it is compared to Forecasting's query.

Again, many thanks to both of you for the generous help and time you put into this!

-Tek