Tricky : SELECT N-to-N "ALL or NOTHING" (please read in)

[LePoulpe]

Hi,

Sorry for the title but I can't find the right words. Don't hesitate to suggest a better one ...

Here is my problem :
I got 2 tables. First one is "Categories" (with a CID as primary key) and second one is "StructuresCategories" (with SCID, CID as primary key). The main idea is that a StructuredCatgory is the intersection of several Category.

I want to be able to select only the StructuredCategories where ALL the Categories match the where clause.
Example : with StructuredCategories (and Categories) :
S1,C1
S1,C2
S2,C1
S2,C3
S3,C2
S3,C3
WHERE CID="C1" OR CID="C2"
I need to find S1 (and only S1).

My several tries gave me only
S1,C1
S1,C2 (and both are OK)
S2,C1
S3,C3 (and both are KO because S2 and S3 are not "complete").

I looked on the net but couldn't find anything (i guess i miss the right keywords).

And finally, I use MySQL 4.0.x and the query will be used several times on each page of a website so need to be really fast.

Any help is appreciated, either on the SQL side or maybe on the design side.

Thanks a lot by advance

Le Poulpe

 

[LePoulpe]

OK, I found a kind of answer.
First I added a column "count" to StructuredCategories :
S1,C1,2
S1,C2,2
S2,C1,2
S2,C3,2
S3,C2,2
S3,C3,2

And I my request is now :

SELECT *
FROM StructuredCategories
WHERE (cid=C1 OR cid=c2)
group by sid
having count(sid) = count

It works but if you find a better way (more efficient or more elegant), I'm still interested.

TIA

 

[mrdenny]

Well you could do it without the new column.

SELECT SCID, count(*)
FROM StructuredCategories
WHERE (cid=C1 OR cid=c2)
group by SCID
having count(sid) = 2

Other than that, I'm not sure of a better way.

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)