SQL syntax to get row value with common column values

Question

I'm using MySql 5 and I would like to know if it's possible to get the follow values using sql syntax.

Get value nodes which are include in group 1 and also in group 2 and 3.

Hosts | groups
   1      1
   1      2
   1      3
   2      1
   3      1 ----->for this example host 1 and 3 are the value which I need
   3      2
   3      3
   4      1

The group 1 is the master list, a mean I need to get all hosts include in group 1 which are include also in group 2 and 3.

If it's possible, could somebody give an example?

Answer 1

The answer with group by hosts having count(distinct groups) = 3 is frequently mentioned, and it does give the right answer. But the following query also works, and usually performs much better if you have an appropriate index. The index should be on (hosts,groups) if I recall.

SELECT t1.hosts
FROM MyTable AS t1
INNER JOIN MyTable AS t2 USING (hosts)
INNER JOIN MyTable AS t3 USING (hosts)
WHERE (t1.groups, t2.groups, t3.groups) = (1, 2, 3)

See my presentation SQL Query Patterns, Optimized for some analysis of this type of query.

Answer 2

You should be able to get the result using a combination of WHERE, GROUP BY and HAVING:

select hosts
from yourtable
where groups in (1, 2, 3)
group by hosts
having count(distinct groups) = 3;

See SQL Fiddle with Demo

Answer 3

You could try something like this:

select distinct Hosts
from myTABLE
where Hosts in (
  select Hosts from myTABLE where Groups = 1)
AND Hosts in (
  select Hosts from myTABLE where Groups = 2)
AND Hosts in (
  select Hosts from myTABLE where Groups = 3)
Order by Hosts