select rows from table1 depending on X rows in table2

MySQL & MariaDB Other MySQL® Questions
1

Hallo,

i have the following situation:

Table1:
id - primary
name - varchar

Table2:
id - primary
Table1_id - foreign of Table1
Table3_id - foreign of Table3

Table3:
id - primary
name - varchar

The problem:
I wanna select all rows from Table1 which have lets say 3 entrys in Table 3. Those 3 entrys should also be selected with a WHERE, means there could be 10, but i just wanna have all rows from Table1 which have 3 specific entrys in Table3

my current solution is to select all entrys from Table3 since i know the values and join to up to Table1. Afterwards i filter the result out in [insert any programming language here] (in my case Python) )

But this relation does have atm around 140.000 entrys and its taking over 1.5 secs to select + the time python needs to loop over all results an delete all not needed stuff. And the big problem is the overhead of entrys im not needing.
Mostly im getting result sets with like 3000+ results, after the filtering there is maybe 1/6 left.

Thats just a part of a whole query, the original query joins over 5 Tables to get all infos we need, but i guess thats just overhead for the problem here …

I wanna have all Results from Table1 which have X specific entrys in Table3

i really hope there is a nice solution or just ideas, anything would help

Goir

2

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 )

3

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

4

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 )

5

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