CODEWITHSUNDEEP
mysql
jhnferraris
jhnferraris

Reputation: 1401

MySQL get all rows based on a specific column value (filter by column)

I have this table A here:

| colA | colB
| 1    | 2
| 2    | 2 
| 3    | 3
| 3    | 2

I want to query all rows where colB is both 3 and 2. In which the result would be:

|colA  | colB
| 3    | 3
| 3    | 2

I tried querying:

select * from A where colB = 2 AND colB= 3

But I keep getting an empty response. Tried OR but it also won't work. I understand that what I experimented is sort of a "row" filtering.

Is there a way to handle "column" filtering?

Upvotes: 0

Views: 233

Answers (2)

sagi
sagi

Reputation: 40491

You can use EXISTS() :

SELECT * FROM YourTable a
WHERE EXISTS(SELECT 1 from YourTable s
             WHERE a.colA = s.colA
              and s.colB in(2,3)
             GROUP BY s.colA having count(distinct s.colB) = 2)

Upvotes: 1

Giorgos Betsos
Giorgos Betsos

Reputation: 72225

Try this:

select *
from A
where colA IN (select colA 
               from A 
               where colB IN (2, 3)
               group by colA
               having count(distinct colB) = 2)

or with a JOIN:

select A.*
from A 
join (select colA 
      from A 
      where colB IN (2, 3)
      group by colA
      having count(distinct colB) = 2
) as t on A.colA = t.ColA

Upvotes: 1

Related Questions