Lets try to answer this question, with a good and simple scenario, with 3 MySQL tables i.e. datetable, colortable and jointable.
first see values of table datetable with primary key assigned to column dateid:
mysql> select * from datetable;
+--------+------------+
| dateid | datevalue |
+--------+------------+
| 101 | 2015-01-01 |
| 102 | 2015-05-01 |
| 103 | 2016-01-01 |
+--------+------------+
3 rows in set (0.00 sec)
now move to our second table values colortable with primary key assigned to column colorid:
mysql> select * from colortable;
+---------+------------+
| colorid | colorvalue |
+---------+------------+
| 11 | blue |
| 12 | yellow |
+---------+------------+
2 rows in set (0.00 sec)
and our final third table jointable have no primary keys and values are:
mysql> select * from jointable;
+--------+---------+
| dateid | colorid |
+--------+---------+
| 101 | 11 |
| 102 | 12 |
| 101 | 12 |
+--------+---------+
3 rows in set (0.00 sec)
Now our condition is to find the dateid's, which have both color values blue and yellow.
So, our query is:
mysql> SELECT t1.dateid FROM jointable AS t1 INNER JOIN jointable t2
-> ON t1.dateid = t2.dateid
-> WHERE
-> (t1.colorid IN (SELECT colorid FROM colortable WHERE colorvalue = 'blue'))
-> AND
-> (t2.colorid IN (SELECT colorid FROM colortable WHERE colorvalue = 'yellow'));
+--------+
| dateid |
+--------+
| 101 |
+--------+
1 row in set (0.00 sec)
Hope, this would help many one.