CODEWITHSUNDEEP
mysql
Vijay Dev
Vijay Dev

Reputation: 27496

MySQL Query - Getting distinct values

There is a table "T" that contains data as shown below:

A   B
---------
3    5   
4    6      
7    10 
8    5 
9    12 
3    6
3    7
8    7

Assuming a given input set of {3,8} as values for A, how to retrieve all distinct values of B for which all values in the input set has an entry?

B
---
5
7

EDIT: I think the question is not clear enough. I want values in B which have a record with all values in the given set as a value for column A. So, B=6 will not be included since there is no record with A=8 and B=6. Hope this makes it clear!

Upvotes: 0

Views: 163

Answers (2)

Lukasz Lysik
Lukasz Lysik

Reputation: 10620

SELECT DISTINCT B 
FROM my_table WHERE A IN (3,8)

EDIT:

SELECT B FROM AB WHERE A = 3
INTERSECT
SELECT B FROM AB WHERE A = 8

INTERSECT give you the rows which occurs in both resultsets.

2nd EDIT:

SELECT B,COUNT(B) 
FROM AB WHERE A IN (3,8) 
GROUP BY B 
HAVING COUNT(B) = 2

You should however modify this in two places: in IN arguments and on the end, in COUNT(B) = ?. ? should be equal the number of the arguments. I hope this will help.

3rd EDIT:

SELECT B,COUNT(B) 
FROM 
(
    SELECT DISTINCT A, B FROM AB
) x
WHERE A IN (3,8) 
GROUP BY B 
HAVING COUNT(B) = 2

This will avoid the duplicate entries problem.

Upvotes: 5

Brad
Brad

Reputation: 748

Basically, you can create two subqueries where you filter out only the rows that are candidates for matching (i.e. A is either 3 or 8). Then join those rows with each other on the value of B, and any matching rows will be what you're looking for. I'm not 100% certain of the syntax for MySQL, but I believe this will work:

SELECT * FROM (SELECT * FROM T WHERE A = 3) t3 INNER JOIN (SELECT * FROM T WHERE A = 8) t8 ON t3.B = t8.B

Upvotes: 0

Related Questions