Select for result both of two column values

All we need is an easy explanation of the problem, so here it is.

Hoping for syntax to select for result that has both of 2 possible column values.

Right now I’m doing
select distinct f1,f2,f3 from table where f3 in (‘val1′,’val2’)

and outputting the text file and then looking for the cases where both val1 and val2 are in the file for the same f1,f2. f3 can be val1, val2, val?… with one line in the table for each of the values. thanks!

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

I’ve mocked up this table…

r f1 f2 f3
1 A B val1
2 A B val2
3 A B val1
4 A E val1
5 A E val2
6 A G val1
7 A G val1

…and I assume you want to find all combinations of (f1,f2) which have 'val1' or 'val2' in f3.

Thus, from that table, you would expect to get the combinations (A,B) and (A,E) but not (A,G) because the last combination doesn’t feature 'val2'.

First, get unique combinations having either 'val1' or 'val2' in f3.

SELECT DISTINCT f1,f2,f3 FROM table1 WHERE f3 IN ('val1','val2')

Once you have that, then group the rows on the first two columns and count how many of each combination you have. You want combinations where there are two (which must be both 'val1' and 'val2'):

SELECT f1,f2 FROM
   (
   SELECT DISTINCT f1,f2,f3 FROM table1 WHERE f3 IN ('val1','val2')
   ) q
GROUP BY f1,f2
HAVING COUNT(f3)=2

This will get you

f1 f2
A B
A E

With nested queries this isn’t going to be very efficient. Optimising depends at least to some extent on the amount of data, its variability and which database you’re using.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply