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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-07-17 : 08:28:41
|
| Piet writes "I have the following situation. 2 master tables, called Person and Skills, with a Many-to-Many relationship, and a join table that resolves the many to many. Assume the following. A person can have multiple skills right. Person "X", with P_ID = 10 have got 3 skills. If I select 3 of the 3 skills from a front end, how do I use it in a querie. I know that using the IN keyword will work, but what if all 3 criteria is kind of a must have. All 3, or 4 fields must have a value, before a row is returned. With the IN keyword, some of the skills doesn't have to be true, and still data is returned.For exampleSelect P_ID, P_NameFrom Person Where P_ID in (select P_ID from JoinTable where Skill_ID in ( x, y,z)if the person haven't got skill x, the querie will still return data, based on the other 2 skills found. All 3 skills must be true, otherwise no data should be returned.I hope to hear from you soonPiet" |
|
|
JustinBigelow
SQL Gigolo
1157 Posts |
Posted - 2002-07-17 : 11:38:21
|
| Not tested, this should be close in theory at least...Select P_ID, P_Name From Person Where P_ID in (select P_ID from JoinTable where Skill_ID in (x, y,z))group by P_IDhaving count(*) = 3You should get one row back for each successful evaluation of the in clause on the subquery. Then do a count of the # of P_ID's returned. If its 3 then they have those three skillshth,Justin Edited by - justinbigelow on 07/17/2002 11:38:48 |
 |
|
|
|
|
|
|
|
Select Statment ---- Join Tables