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?

Example image

This seems like a pretty basic question (no offense). Could you confirm that you just want to get the values of col1 where col2 has a value of `1,2,3, or 4`? Or do you mean you want the lowest values? Or something else? Because in your first example, col1 values would 1 or 2, since col2 has 1 and 2 next to both a 1 and 2 in col1. — Anthony, Mar 25 '12 at 08:26
I just want to get the values of col1 that exactly has "1,2,3,4" as col2. In this scenario it's "1" in col1. But i get "1,2" with "SELECT DISTINCT col1 FROM MyTable WHERE col2 IN ('1', '2', '3', '4')" statement. — platypus, Mar 25 '12 at 08:38

score 5 · Accepted Answer · edited May 23 '17 at 12:24

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

Uwe Keim · Answer 2 · 2012-03-25T09:01:55.430

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.

edited Mar 25 '12 at 09:01

answered Mar 25 '12 at 08:19

Uwe Keim

39,551
56
175
291

I only want the col1 values that match with those multiple col2 values. For ex: that statement should only return col1's "1" – platypus Mar 25 '12 at 08:22
@gcx I think I do not fully understand your question then. – Uwe Keim Mar 25 '12 at 08:25
@gcx Do you mean that if col2 is 1, 2, 3, or 4, you only want the query to return the value that shows up the most in col1? Or only want the value that shows up more than once? I think you'll need to give a use case/ real world example – Anthony Mar 25 '12 at 08:29
@UweKeim I'm sorry couldn't explained it clearly, let me eloborate. 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. – platypus Mar 25 '12 at 08:30
@UweKeim By the way, the example you give above, returns 1 and 2 not just 1. – platypus Mar 25 '12 at 08:32
1

@gcx Thanks, I adjusted the example. – Uwe Keim Mar 25 '12 at 09:02

score 0 · Answer 3 · answered Mar 25 '12 at 08:38

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.

It's not sequence related if that's what you're asking. It can be "1,3,2,4" or "4,3,1,2" doesn't matter. Unfortunately, Uwe Keim's answer gives "1,2" as a result. I want it to show "1" as a result. — platypus, Mar 25 '12 at 08:44

Checking Multiple Column Value

3 Answers3