Extract multiple similar rows for each individual column type

Question

Enter

+--------+------+------+ | col1 | col2 | col3 | +--------+------+------+ | apple | d | 10 | | apple | d | 44 | | apple | e | 55 | | orange | d | 99 | | orange | c | 33 | | orange | d | 10 | | banana | e | 55 | | banana | d | 10 | +--------+------+------+ 

Required conclusion

 +--------+------+------+ | col1 | col2 | col3 | +--------+------+------+ | apple | d | 10 | | orange | d | 10 | | banana | d | 10 | +--------+------+------+ 

We will test col2 and col3 for N different types of fruits.

We want to list only those where the values ​​col2 and col3 are the same, and the string is present for all fruits

Extended explanation:

You can think of it this way: -

STEP 1

Separate all the different types of fruits: -

Apple: -

 +-------+------+------+ | col1 | col2 | col3 | +-------+------+------+ | apple | d | 10 | | apple | d | 44 | | apple | e | 55 | +-------+------+------+ 

Orange: -

 +--------+------+------+ | col1 | col2 | col3 | +--------+------+------+ | orange | d | 99 | | orange | c | 33 | | orange | d | 10 | +--------+------+------+ 

Banana: -

 +--------+------+------+ | col1 | col2 | col3 | +--------+------+------+ | banana | e | 55 | | banana | d | 10 | +--------+------+------+ 

STEP 2: -

Now select only those rows that

• SAME col2 and col3

and

• It is present in all types of fruits.

OBSERVATIONS: -

“apple e 55” and “banana e 55” have the same Col2 and COL3 values, but it is not selected because “orange e 55” is not.

If you use temporary tables, please make sure that it should be shared. It must support N amount of fruit.

Note: - This is not the task of the student :D I explain this in simple words, because it is part of a long and long request, and I have a ZERO idea on how to solve it. I use the method of creating temporary tables, but I am having some problems. This was not common. Therefore, I believe that there may be some better solution to this problem.

Answer 1

Basically, you can get a record that is present in all fruits, regardless of name through this request,

 SELECT col2, col3 FROM tableName GROUP BY col2, col3 HAVING COUNT(*) = (SELECT COUNT(DISTINCT col1) FROM tableName) 

• SQLFiddle Demo

The total number of entries in a group of two columns: col2 and col3 should be equal to the total number of fruits. SELECT COUNT(DISTINCT col1) FROM tableName .

So, to get all the records that have a combination that is present in all the fruits, we need to JOIN it with the table itself.

 SELECT a.* FROM tableName a INNER JOIN ( SELECT col2, col3 FROM tableName GROUP BY col2, col3 HAVING COUNT(*) = (SELECT COUNT(DISTINCT col1) FROM tableName) ) b ON a.col2 = b.col2 AND a.col3 = b.col3 

• SQLFiddle Demo