SELECTING with multiple WHERE conditions on same column

by

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.

More Related Contents:

  1. SQL select only rows with max value on a column [duplicate]
  2. WHERE vs HAVING
  3. Detect if value is number in MySQL
  4. MySQL “Group By” and “Order By”
  5. SQL Query to get column values that correspond with MAX value of another column?
  6. MySQL Select last 7 days
  7. How to select data where a field has a min value in MySQL?
  8. mysql count group by having
  9. Using LIMIT within GROUP BY to get N results per group?
  10. Can you use an alias in the WHERE clause in mysql?
  11. Select values that meet different conditions on different rows?
  12. MySQL error: key specification without a key length
  13. mysql update column with value from another table
  14. Select row with most recent date per user
  15. SQL query: Delete all records from the table except latest N?
  16. Rename a column in MySQL
  17. The total number of locks exceeds the lock table size
  18. Mysql 1050 Error “Table already exists” when in fact, it does not
  19. MySQL “IN” operator performance on (large?) number of values
  20. How to get the latest record in each group using GROUP BY? [duplicate]
  21. How do I count columns of a table
  22. Difference between two table structure
  23. Difference between SET autocommit=1 and START TRANSACTION in mysql (Have I missed something?)
  24. How to retrieve JSON data from MySQL?
  25. MySQL equivalent of Oracle’s SEQUENCE.NEXTVAL
  26. Why MySQL is giving error “Not allowed to return a result set from a function”?
  27. When to use a left outer join?
  28. SQL query with avg and group by
  29. Optimistic locking in MySQL
  30. Insert and set value with max()+1 problems

Leave a Comment