0

is there an easy way to do multiple AND statments like the IN clause does for OR?

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
waa1990
  • 2,365
  • 9
  • 27
  • 33
  • Can you give an example of what you're trying to do? – Jonathan Rich Dec 09 '11 at 19:35
  • 1
    When setting up exclusions, `NOT IN ()` is technically a string of `AND` operations, rather than `OR` operations... – Michael Berkowski Dec 09 '11 at 19:36
  • Yeah, second jonathan's; it'll help if you describe to us what it is you want to achieve. Perhaps there are other ways to get what you need, and having table structures, sample data, sample expected output and your best way of describing your goal helps a lot... – Nonym Dec 09 '11 at 20:06

3 Answers3

0

You wish to use the ALL keyword?

http://dev.mysql.com/doc/refman/5.5/en/all-subqueries.html

Tim Lehner
  • 14,813
  • 4
  • 59
  • 76
  • In MySQL, `ALL` only works if you use a sub-query to get the values, so it's not exactly the same as `IN` – bhamby Dec 09 '11 at 20:14
0

Is not possible in general,
a column can only store one value,
you won't have a situation when status = 1 AND status =2

The exception case is set data type

ajreal
  • 46,720
  • 11
  • 89
  • 119
0

This is a common enough question on Stack Overflow that I'm creating a new tag for it, sql-match-all. I'll go back and try to tag some of my old answers related to this problem.

Based on your brief description, I assume you mean you want to return a group of rows that match all of a set of values, for example articles that are tagged (php, mysql, performance). The IN() predicate does not require that all three tags are matched, but a given row of data only has one tag, and a WHERE clause can only test one row at a time.

There are two popular solutions for this problem. The first is to count how many distinct tags in the group of rows, and if it's equal to the list of tags you're looking to match, then this passes the test.

SELECT t.article
FROM Tags t
WHERE t.tag IN ('php', 'mysql', 'performance')
GROUP BY t.article
HAVING COUNT(DISTINCT tag) = 3;

The other solution is to self-join as many times as the number of tags you're looking for. By using INNER JOIN, you restrict the result set to only those groups that match.

SELECT t1.article
FROM Tags t1
INNER JOIN Tags t2 USING (article)
INNER JOIN Tags t3 USING (article)
WHERE (t1.tag, t2.tag, t3.tag) = ('php', 'mysql', 'performance');

Which solution is better can depend on the number of tags you want to match, how convenient it is to build this query in your application, and also specific optimizations that your brand of RDBMS is good at. The latter solution is usually much faster in MySQL, for example, because MySQL tends to write temp tables for GROUP BY queries. But you should test both query types in your database to make sure.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • I suggest creating a tag wiki for your new tag. Would be a useful place to list some of the more common forms... – derobert Dec 09 '11 at 19:54