Announcement

Announcement Module
Collapse
No announcement yet.

select rows from table1 depending on X rows in table2

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • select rows from table1 depending on X rows in table2

    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 )

    Comment


    • #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

      Comment


      • #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 )

        Comment


        • #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

          Comment

          Working...
          X