#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2014
    Posts
    40
    Rep Power
    5

    SQL Query - SELECT with AND clause question...


    Hi All,

    I have the below SQL SELECT Script....

    Code:
    SELECT c.Title, c.Forenames, c.Surname, c.Company, cc.Client_Category_ID, lc.Category_ID , lc.Category 
    FROM tblClient AS c 
    INNER JOIN tblClient_Category AS cc ON cc.Client_ID = c.Client_ID 
    INNER JOIN tblLookUp_Category AS lc ON lc.Category_ID = cc.Category_ID 
    WHERE cc.Category_ID = 6 
    GROUP BY c.Surname, c.Client_ID ASC
    Which returns results OK, and I want to filter it down further, but when I try and run:

    Code:
    SELECT c.Title, c.Forenames, c.Surname, c.Company, cc.Client_Category_ID, lc.Category_ID , lc.Category 
    FROM tblClient AS c 
    INNER JOIN tblClient_Category AS cc ON cc.Client_ID = c.Client_ID 
    INNER JOIN tblLookUp_Category AS lc ON lc.Category_ID = cc.Category_ID 
    WHERE (cc.Category_ID = 6) AND (cc.Category_ID = 6)
    GROUP BY c.Surname, c.Client_ID ASC
    It returns no results, when I know there should be at least one....

    Many Thanks,
    Graham
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,794
    Rep Power
    4331
    Code:
    WHERE (cc.Category_ID = 6) AND (cc.Category_ID = 6)
    okay, two comments

    first of all, you are asking the database to check each Category_ID and filter out all values that aren't 6, as well as all values that aren't 6

    even the mysql optimizer, dumb as it is, will collapse that back to only one test, just like in your first query

    second comment -- suppose you actually had two values to filter on, say for example like this --
    Code:
    WHERE (cc.Category_ID = 6) AND (cc.Category_ID = 23)
    now what you're saying is you want every value of Category_ID that is equal to 6 and at the same time equal to 23

    clearly, no rows are going to pass that test



    p.s. those parentheses are unnecessary and merely add noise
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,794
    Rep Power
    4331
    also, you're going to run into difficulties analyzing your results if a client has multiple categories, because with your SELECT list and GROUP BY, you're only going to show one of them
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,794
    Rep Power
    4331
    tell the truth, isn't this what you really want --
    MySQL Query - Last Column of Select as a nested Select?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo