mysqlsql-match-all

Checking Multiple Column Value


I'm trying to get col1 values for certain multiple col2 values. For example: I want to see col1 values for col2's "1, 2, 3, 4" values (that is "1" in col1). Another ex: col1 values for col2's "1, 2" are "1, 2". How can i manage to do this in SQL syntax?

Example image


Solution

  • What you want is called relational division. There are several ways to accomplish it. Check this question which has more than ten different solutions for a similar problem - including benchmarks: How to filter SQL results in a has-many-through relation

    Here's one of the ways (it assumes that (col1, col2) combination is Unique):

    SELECT col1 
    FROM tableX 
    WHERE col2 IN (1, 2, 3, 4)
    GROUP BY col1
    HAVING COUNT(*) = 4            --- the size of the above list
    

    and another:

    SELECT t1.col1 
    FROM tableX AS t1
      JOIN
         tableX AS t2
             ON  t2.col1 = t1.col1
             AND t2.col2 = 2
      JOIN
         tableX AS t3
             ON  t3.col1 = t1.col1
             AND t3.col2 = 3
      JOIN
         tableX AS t4
             ON  t4.col1 = t1.col1
             AND t4.col2 = 4
    WHERE t1.col2 = 1