CODEWITHSUNDEEP
mysql
Tsitna FZ
Tsitna FZ

Reputation: 11

MySQL select one row from field WHERE condition is in multiple rows

Tried to find the answer, but still couldn't.. The table is as follows:

  name,    room_chat
  Ratna          2
  Ima            2
  Ratna          3
  Yanis          3

i need something like this if i select name from table where (name='Ratna' and name='Ima') i get a result 

room chat
2

Thanks in advance

Upvotes: 0

Views: 153

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 94894

You have several rows, but you want to look at a set of rows. This is called aggregation. In your case you want to look at each room chat, so you group by room_chat. You want to know whether for a room chat you find both names, so count the names.

The query:

select room_chat
from table 
where name in ('Ratna','Ima')
group by room_chat
having count(distinct name) = 2;

Upvotes: 1

donlys
donlys

Reputation: 450

Use in clause as below: 

select distinct a.room_chat from tableA a where a.name in ('Ratna', 'Ima')

Upvotes: 1

Related Questions