AND question,

Hello,

Let's say i have this table tblEmplCat:

emplID CatID
1 3
1 4
1 5
2 3
2 4
3 4

Now i wan't all the emplID witch have p.e. 3 AND 4 as CatID
so the result should be 1 and 2.

SELECT emplID FROM tblEmplCat where CatID=3 and catID=4

this won't work of course, but how can i achieve this.

Thanks in advance.SELECT DISTINCT EmplId FROM tblEmplCat WHERE CatID In (3,4)

??|||SELECT DISTINCT(emplID) FROM tblEmplCat WHERE emplID IN (SELECT DISTINCT(emplID) FROM tblEmplCat WHERE CatID = 3) AND emplID IN (SELECT DISTINCT(emplID) FROM tblEmplCat WHERE CatID = 4)|||Pete,

You prefer that syntax? Do a showplan on that and see what it is doing|||Brett, your code is equivalent to an OR, not an AND.|||Brett-

I realize it's not a great performing query, but what is an alternative? He doesn't want the emplId = 3 record as it doesn't have both CatIds 3 AND 4.|||OK, read it wrong

SELECT DISTINCT EmplId
FROM tblEmplCat a JOIN tblEmplCat b
ON a.empl_id = b.Empl_id
WHERE a.CatID = 3
AND b.CatID = 4

??|||Now that one I like.
May get messy extended to more than two values, but probably about as good as one will get given the strict interpretation of the original post.|||select EmplId
from tblEmplCat
where CatID in ( 3 , 4 )
group
by EmplId
having count(*) = 2 quite easy to extend this pattern to 3, or 4, or more values

whereas the join approach turns into a dog's breakfast

still not convinced? try this -- find all EmplIDs which have at least 3 of the following 5 Catid values...

:)|||Ah, that is more extendable, but the logic fails if EmpID and CatID are not a unique composite key. For instance, if an EmpID has two records with CatID = 3, then it will pass your test.|||... logic fails if EmpID and CatID are not a unique composite key. that is correct, sir

with that type of design (many-to-many relationship table without the obnoxious surrogate auto_increment key), i feel it is safe to assume the required composite uniqueness

wanna see how to change the HAVING clause if this turns out not to be the case?

:) :)|||...having count(distinct CatId) = 2
...and there you have a nice, complete solution.