Results 1 to 12 of 12

Thread: AND question,

  1. #1
    Join Date
    Dec 2006
    Posts
    1

    Unanswered: 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.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    SELECT DISTINCT EmplId FROM tblEmplCat WHERE CatID In (3,4)

    ????
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Apr 2006
    Posts
    2
    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)

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Pete,

    You prefer that syntax? Do a showplan on that and see what it is doing
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Brett, your code is equivalent to an OR, not an AND.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Apr 2006
    Posts
    2
    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.

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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

    ????
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    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...

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by blindman
    ... 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?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Code:
    ...having count(distinct CatId) = 2
    ...and there you have a nice, complete solution.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •