SELECTING with multiple WHERE conditions on same column

Question

Ok  I think I might be overlooking something obvious simple here    but I need to write a query that returns only records that match multiple criteria on the same column     My table is a very simple linking setup for applying flags to a user      ID   contactid  flag        flag type  ----------------------------------- 118  99         Volunteer   1  119  99         Uploaded    2  120  100        Via Import  3  121  100        Volunteer   1   122  100        Uploaded    2   etc    in this case you ll see both contact 99 and 100 are flagged as both  Volunteer  and  Uploaded      What I need to be able to do is return those contactid s ONLY that match multiple criteria entered via a search form   the contactid s have to match ALL chosen flags    in my head the SQL should look something like   SELECT contactid   WHERE flag    Volunteer      AND flag    Uploaded       but    that returns nothing    What am I doing wrong here

User · Answer

select purpose pname company cname from purpose Inner Join company on purpose id company id where pname  Fever  and cname  ABC  in     select mname   from medication   where mname like  A     order by mname

User · Answer

Use this  For example   select   from ACCOUNTS DETAILS where ACCOUNT ID 1001 union select   from ACCOUNTS DETAILS where ACCOUNT ID 1002

User · Answer

Consider using INTERSECT like this   SELECT contactid WHERE flag    Volunteer   INTERSECT SELECT contactid WHERE flag    Uploaded    I think it it the most logistic solution

User · Answer

Sometimes you can t see the wood for the trees     Your original SQL     SELECT contactid   WHERE flag    Volunteer      AND flag    Uploaded       Should be   SELECT contactid   WHERE flag    Volunteer      OR flag    Uploaded

User · Answer

AND will return you an answer only when both volunteer and uploaded are present in your column  Otherwise it will return null value     try using OR in your statement      SELECT contactid  WHERE flag    Volunteer  OR flag    Uploaded

User · Answer

Use     SELECT t contactid     FROM YOUR TABLE t    WHERE flag IN   Volunteer    Uploaded   GROUP BY t contactid   HAVING COUNT DISTINCT t flag    2   The key thing is that the counting of t flag needs to equal the number of arguments in the IN clause   The use of COUNT DISTINCT t flag  is in case there isn t a unique constraint on the combination of contactid and flag -- if there s no chance of duplicates you can omit the DISTINCT from the query     SELECT t contactid     FROM YOUR TABLE t    WHERE flag IN   Volunteer    Uploaded   GROUP BY t contactid   HAVING COUNT t flag    2

User · Answer

SELECT contactid  Count     FROM  lt YOUR TABLE gt  WHERE flag in   Volunteer   Uploaded     GROUP BY contactid  HAVING count    gt 1

User · Answer

You can either use GROUP BY and HAVING COUNT          SELECT contact id FROM your table WHERE flag IN   Volunteer    Uploaded        GROUP BY contact id HAVING COUNT      2 --    must match number in the WHERE flag IN       list    assuming contact id  flag is unique    Or use joins   SELECT T1 contact id FROM your table T1 JOIN your table T2 ON T1 contact id   T2 contact id AND T2 flag    Uploaded  --    more joins if necessary WHERE T1 flag    Volunteer    If the list of flags is very long and there are lots of matches the first is probably faster  If the list of flags is short and there are few matches  you will probably find that the second is faster  If performance is a concern try testing both on your data to see which works best

User · Answer

Change AND to OR  Simple mistake  Think of it like plain English  I want to select anything with that equals this or that

User · Answer

Try to use this alternate query   SELECT A CONTACTID  FROM  SELECT CONTACTID FROM TESTTBL WHERE FLAG    VOLUNTEER  A     SELECT CONTACTID FROM TESTTBL WHERE FLAG    UPLOADED   B WHERE A CONTACTID   B CONTACTID

User · Answer

can t really see your table  but flag cannot be both  Volunteer  and  Uploaded   If you have multiple values in a column  you can use  WHERE flag LIKE   Volunteer   AND flag LIKE   UPLOADED     not really applicable seeing the formatted table

[mysql] SELECTING with multiple WHERE conditions on same column

Examples related to mysql

Examples related to sql

Examples related to aggregate-functions

Examples related to where-clause