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!

13.10.2009 15:14:51
2 ОТВЕТА
РЕШЕНИЕ
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.

5
14.10.2009 13:57:39
+1: Short and sweet. The "IN" operator solves many many problems: though the WHERE clause could also be written as "WHERE (A = '3' OR A = '8')", using IN is clearly more readable and concise.
Satanicpuppy 13.10.2009 15:21:01
But MySQL does not support intersects :(
Vijay Dev 13.10.2009 15:32:33
Even assuming that intersect is available, this method will become unwieldy if the input set has too many values in it.
Vijay Dev 13.10.2009 15:35:12
Another solution. Much better I think.
Lukasz Lysik 13.10.2009 15:41:32
I think this solution doesn't work if duplicated rows are allowed in this table. In other words, if the row {A:3, B:7} were to appear twice, this query would return {7,2}, which is wrong.
Brad 14.10.2009 03:10:42

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

0
14.10.2009 03:16:48