How To Get An Id Associated With At Least All Contents?

Suppose we have the database: ----------- | A -|- B | |----|----| | 1 | 1 | | 1 | 2 | | 2 | 1 | ----------- Where A and B is the primary key. Suppose we want to get all As t

Solution 1:

GROUP BY solution, return all a's that have more than 1 different b value in (1,2):

select a fromtablewhere b in (1,2)
groupby a
havingcount(distinct b) >1

Or, JOIN solution:

selectdistinct a
from (select a fromtablewhere b =1) t1
join (select a fromtablewhere b =2) t2
  on t1.a = t2.a

Or an INTERSECT solution:

select a fromtablewhere b =1intersectselect a fromtablewhere b =2

Edit:

GROUP BY query that perhaps is faster then the HAVING count distinct version:

select a fromtablewhere b in (1,2)
groupby a
havingmax(b) <>min(b)

Solution 2:

You can use the group by method from jarlh or make a Join with a 'distinct':

selectdistinct a
from (select a fromtablewhere b =1) t1
join (select a fromtablewhere b =2) t2
  on t1.a = t2.a

Solution 3:

Something like this (assuming that you need to filter by the specific IDs in B.

SELECTDISTINCT A
FROMTableAS T
WHEREEXISTS (SELECT1fromTableWHERE Table.A = T.A and B =1)
ANDEXISTS (SELECT1fromTableWHERE Table.A = T.A and B =2)

Solution 4:

Try this

SELECT A
FROMTableWHEREEXISTS (
    SELECT1FROMTable t1
    WHERE t1.A = Table.A 
        AND t1.B =1
)
ANDEXISTS (
    SELECT1FROMTable t2 
    WHERE t2.A = Table.A 
        AND t2.B =2
)

Solution 5:

• A cannot be the primary key here, since the column contains duplicates.

• One possible solution:

  SELECT * FROM (SELECT A, group_concat(B, ',') AS C FROM tab GROUP BY A) s WHERE s.C = "1,2";