var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: AND question,
Let's say i have this table tblEmplCat:
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)
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.
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.
quite easy to extend this pattern to 3, or 4, or more values
where CatID in ( 3 , 4 )
having count(*) = 2
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.
that is correct, sir
Originally Posted by blindman
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?
...and there you have a nice, complete solution.
...having count(distinct CatId) = 2