Goir,

What is Table2? You mentioned it's structure, but did not tell how you use it in query.
As I understood, this table stores relationship between IDs from Table1 and Table3, right? And if so, this query will work for you:

SELECT Table1_id,count(Table1_id) as entries_in_Table3 from Table2 GROUP BY Table1_id HAVING entries_in_Table3=3;


Or if I misunderstood you, then please give more details, and maybe some data samples )

OK, here we go )

Table1
id - pk
value

Table2
pk
foreign_table1
value

its a 1:m relation

i wanna have all rows from Table1 that have X (lets say 3) specific entrys in Table2

Example:

Table1 Data
id | value
1 | MySQL
2 | Browser
3 | Upload

Table2 Data
id | foreign_table1 | value
1 | 1 rocks
2 | 1 sometimes
3 | 2 Explorer
4 | 2 Internet
5 | 2 Micro$oft
6 | 2 Firefox
7 | 1 Firefox

Now i wanna have all Rows from Table1 that 3 specific entrys in Table2
Explorer, Internet and Mico$oft

would give me the Browser row from Table1

and
Firefox, rocks
would give me the MySql row from Table1

edit:
and just Firefox would result in row1 and rows2 from Table1

------------
Goir

Goir,

Here is table data (from your example):


mysql> select * from st664_1;+----+---------+| id | value |+----+---------+| 1 | MySQL || 2 | Browser || 3 | Upload |+----+---------+3 rows in set (0.00 sec)



mysql> select * from st664_2;+----+----------------+-----------+| id | foreign_table1 | value |+----+----------------+-----------+| 1 | 1 | rocks || 2 | 1 | sometimes || 3 | 2 | Explorer || 4 | 2 | Internet || 5 | 2 | Micro$oft || 6 | 2 | Firefox || 7 | 1 | Firefox |+----+----------------+-----------+7 rows in set (0.00 sec)


And this is query, which returns row from first table, which has requested number of related records in second table:


mysql> select * from st664_1 where id in (select foreign_table1 from st664_2 group by foreign_table1 having count(foreign_table1)=4);+----+---------+| id | value |+----+---------+| 2 | Browser |+----+---------+1 row in set (0.00 sec)



mysql> select * from st664_1 where id in (select foreign_table1 from st664_2 group by foreign_table1 having count(foreign_table1)=3);+----+-------+| id | value |+----+-------+| 1 | MySQL |+----+-------+1 row in set (0.00 sec)


So queries are returning output, which you asked for, and there is no need to filter output by python script.
Here is explaining of this query:


mysql> EXPLAIN select * from st664_1 where id in (select foreign_table1 from st664_2 group by foreign_table1 having count(foreign_table1)=4); +----+--------------------+---------+------+---------------+ ------+---------+------+------+----------------------------- ----+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+---------+------+---------------+ ------+---------+------+------+----------------------------- ----+| 1 | PRIMARY | st664_1 | ALL | NULL | NULL | NULL | NULL | 3 | Using where || 2 | DEPENDENT SUBQUERY | st664_2 | ALL | NULL | NULL | NULL | NULL | 7 | Using temporary; Using filesort | +----+--------------------+---------+------+---------------+ ------+---------+------+------+----------------------------- ----+2 rows in set (0.00 sec)


As it's using filesort and creates temporary table, it is not very fast. If it's ok for you to add index to column foreign_table1, it can help to speed up those queries:


mysql> create index foreign_table1_idx ON st664_2(foreign_table1);Query OK, 7 rows affected (0.04 sec)Records: 7 Duplicates: 0 Warnings: 0


And let's look at explain now:

mysql> EXPLAIN select * from st664_1 where id in (select foreign_table1 from st664_2 group by foreign_table1 having count(foreign_table1)=4); +----+--------------------+---------+-------+--------------- +--------------------+---------+------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+---------+-------+--------------- +--------------------+---------+------+------+-------------+| 1 | PRIMARY | st664_1 | ALL | NULL | NULL | NULL | NULL | 3 | Using where || 2 | DEPENDENT SUBQUERY | st664_2 | index | NULL | foreign_table1_idx | 5 | NULL | 7 | Using index | +----+--------------------+---------+-------+--------------- +--------------------+---------+------+------+-------------+2 rows in set (0.00 sec)


You see that now it uses only index, and it's faster then doing filesort+temporary. Please note that if you often add data to second table, then maybe it's not a good option for you..

P.S.
Table1 => st664_1
Table2 => st664_2
Sorry for that, but I try to keep table names in my test db in order )

yeah, thats what i needed ... not exactly but u gave me the hint to it ... at least now i know whats HAVING good for )

And if i think about it, its not that hard to find out by myself ... anyway thanks, you saved me a lot of time ...

Heres what i came up with, since i cant just use the HAVING count(...) there are plenty of entrys in table1 having 3 entrys in table2 ...

Tables are the same as yours.

For just Firefox:

mysql> SELECT b.foreign_table1, a.value -> FROM st664_2 as b -> LEFT JOIN st664_1 a ON a.id = b.foreign_table1 -> WHERE b.value = 'Firefox' -> GROUP BY foreign_table1 -> HAVING count(b.foreign_table1)=1;+----------------+---------+| foreign_table1 | value |+----------------+---------+| 1 | MySQL | | 2 | Browser | +----------------+---------+2 rows in set (0.00 sec)


and with 3 Explorer, Internet and Miro$oft

mysql> SELECT b.foreign_table1, a.value -> FROM st664_2 as b -> LEFT JOIN st664_1 a ON a.id = b.foreign_table1 -> WHERE b.value = 'Explorer' OR b.value = 'Internet' OR b.value = 'Micro$oft' -> GROUP BY foreign_table1 -> HAVING count(b.foreign_table1)=3;+----------------+---------+| foreign_table1 | value |+----------------+---------+| 2 | Browser | +----------------+---------+1 row in set (0.00 sec)


Goir