Checking Multiple Column Value

Question

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?

Answer 1

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

Answer 2

Probably this could help:

SELECT col1 FROM MyTable WHERE col2 IN ('1', '2', '3', '4')

This returns all rows where col2 is either "1", "2", "3" or "4".

I.e. as of your example the above query would return

col1
---------
1
1
1
1
2
2

If you add a DISTINCT clause, you only get distinct col1 values:

SELECT DISTINCT col1 FROM MyTable WHERE col2 IN ('1', '2', '3', '4')

Would return

col1
---------
1
2

In your example.

Answer 3

You cannot do what you are explaining with SQL. Judging by your comment:

I want to show col1 values that "must" have "1,2,3,4" in their col2. In the example, col1's "1" value has "1,2,3,4", col1's "1" value only has "1,2" in their col2 thus with this statement i should only see col1's "1" value as result. However, it also gives col1's "2" value

I take it that sequences are important. As each row is not unique based on where it is in your set of data, you cannot simply do it with SQL. You will have to load your data set with some programming language (C, java, php... whatever you like), and loop through your data set and check for these sequences in this manner.