Another SQL syntax challenge

NOTE this is a new question, I know I showed these 3 tabels before.

Im having difficulty writting this sql. I have the three following tables. <br>
I want to be able to query all users in tblUsers who have all of a given selection of skills (tblSkills)

Senerio:
For example the query form would list all the skills from tblSkill with a check box next to each skill. The return for the form should list all users from tblUser who have EACH AND EVERY skill checked.


tblUser................tblUserToSkill.........tblSkill
+----+------+.....+----+-----+.....+----+-------+
| UID | USER |.....| UID | SID |.....| SID | SKILL |
+----+------+.....+----+-----+.....+----+-------+
| 124 | JOHN |.....| 124 | 100 |.....| 100 | SINGS |
| 258 | CARL |.....| 124 | 200 |.....| 200 | DANCE |
| 300 | LENY |.....| 300 | 100 |.....| 300 | BARKS |
+----+------+.....| 258 | 300 |.....+----+--------+
........................+-----|-----+


For the life of me I cannot figure out a sql or programming solution to this one. We'll if my life did count on it, I guess I could write the 60 so lines of code to jump around this problem. But it would be messy.

Is the Skills table gonna be changing??...just like you stated in your previous question? Or is it going to remain static w/only those three skills? Either way this should work...

very good, NULL

but you somehow have to work it so that it's not checking all the skills in tblSkill, but merely the ones selected in the checkboxes (or whatever)

let's say that only 100 and 200 were selected

thus a user could actually have more skills than the ones selected

so therefore you need something like this --

and of course if you look at it even closer, you don't need to count the tblSkill rows at all, just count the number of selections

but wait -- what if the user has 100 and 300? that's 2 distinct, and the subquery from tblSkill returns 2 also...

so that's obviously not going to give a correct answer

i think you must add a WHERE clause as well

Ah...I did have some pre-thoughts about what your bringing up, and I think we just have a diff interpretations of what the poster wants. This is where my focus was when I read his need... (the numbering 1) and 2) are my own) ...you're getting into a scenario where not all skills are listed, this is what I read to make me code otherwise though...

I'm interpreting that...
1) Every skill will be listed
2) The poster only wants records returned where the user has all the skills available...defined as:

...so based off that, I'm accounting for every skill in my count.

Another reason though, is that if the poster does get away from listing all the skills availabe, we can't really just hardcode the skills available from the checkbox group like that, @least it wouldn't be what I would do. If the poster is going to get into changing what skills are listed, then wouldn't we have to start touching the front end stuff to figure whether or not all of the choice boxes are selected? Not sure what the front end is though, maybe using a form writer directly from the DBMS or a front tier UI.

long explanation, so i will give you a short reply:

" ... EACH AND EVERY skill checked"

there seems little reason to assume that all skills will be listed and the user will always check all of them

if that's the case, then why even bother with the checkboxes at all

so yeah, the user is free to check any combination of checkboxes -- all will be listed, but not all will always be checked

then you said "we can't really just hardcode the skills available from the checkbox group like that"

well, no, there would be some sort of scripting logic which would examine which checkboxes have been checked, and string their values together -- in effect we're not hardcoding them, the script is doing it dynamically, but as far as the databse is concerned, by the time the query hits the database, they are hardcoded

Personally I don't see how this isn't sufficient to assume that every skill would be posted.


I am assuming that every skill will be listed.
I am not assuming that every skill will be checked everytime.

And it seems you agree??....

Not saying you're wrong, not by any means...would like to hear what the poster is really looking for.

yes, every skill will be listed on the form, but not all skills will be checked

so therefore the sql that gets passed to the database must include some way of identifying just those that have been checked

my query allows for that, but your query doesn't

sorry

man you've lost me. Let's get on the same foot, I think I'm thinking on diff terms than you. Let's take a scenerio.

10 skills in the tblSkills table.

We both agree all will be available to be checked.

The poster only wants users that have checked all 10 skills.

The skills checked will show up in the tblUserToSkill table.

So if the user only selects 5 skills of all 10 that are available...my query would exclude that user, which is what the poster wants, no?

i will keep trying until you get it

10 skills listed on the form

the user checks 5 of them

the poster only wants those userids pulled from the database where those 5 skills have been checked

i think you are confusing the user of the form with the userids in the table

the key part of the original poster's question is "... to be able to query all users in tblUsers who have ..."

AHHHHH!......yes ok, that's exactly what I was doing! I was thinking the User of the form was a tblUser.UID. ...and the whole time thought that all 10 skills were available on the form, when they (a UID) selected all 10, return 'em, else don't. Gosh...maybe it's time to get some sleep now

1) The tlbSkills is ever changing. I would never create a table for data that didn't have the potential for change. I would hard code that data.

2) Any combination of check boxes can be checked (that's what check boxes are for). The query must return every tblUser.user who has ALL skills that are check.

HERE is my attempt: Unfortunately it didn't work. Yes it contains php code but its tight code
<?
$sql='SELECT * FROM tblUser WHERE 1';
foreach ($_POST as $k => $v) {
$sql.='AND tblUser.UID IN (SELECT UID FROM tblSKills WHERE SID='.$v.') ';
}
?>


SQL Consultant
Your code worked! I'm just starting to grasp how it works.
You rule!

Thanks.