×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

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

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

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

(OP)
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

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

(OP)
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

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

Well you could do it without the new column.

CODE

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)

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close