Professional Web Applications Themes

speedest way to handle a select table1,count(table2) where table1.idtable1=table2.idtable1 ? - MySQL

Hello I have a list of item in TABLE1, and a list of sub item in TABLE2 each item of TABLE1 can have 0 or more sub item i need a query : select TABLE1.x, count( TABLE2.subitem) i guess the natural way should a TABLE1 left outer join TABLE2 with a count(*) and a group by TABLE1.* but i dont like the "group by table1.* " mysql does not like correlated query in from, so i can not write : select TABLE1.*, nbsubitem from TABLE1, (select count(*) as nbsubitem from TABLE2 where TABLE2.iditem=TABLE1.iditem) so is there a rewriting of this ...

  1. #1

    Default speedest way to handle a select table1,count(table2) where table1.idtable1=table2.idtable1 ?

    Hello

    I have a list of item in TABLE1, and a list of sub item in TABLE2
    each item of TABLE1 can have 0 or more sub item

    i need a query :
    select TABLE1.x, count( TABLE2.subitem)

    i guess the natural way should a TABLE1 left outer join TABLE2 with a
    count(*) and a group by TABLE1.*
    but i dont like the "group by table1.* "

    mysql does not like correlated query in from, so i can not write :
    select TABLE1.*, nbsubitem from TABLE1, (select count(*) as nbsubitem from
    TABLE2 where TABLE2.iditem=TABLE1.iditem)

    so is there a rewriting of this imaginary query that can produce the desired
    column ?


    patrice Guest

  2. #2

    Default Re: speedest way to handle a select table1,count(table2) where table1.idtable1=table2.idtable1 ?

    On 14 Jun, 09:36, "patrice" <fr>
    wrote: 
    Why don't you like this?
    Why would you group by table1.* when you are querying table1.x?
     
    Why is this any better than a LEFT JOIN?
     
    Why do you want to re-write? What uis wrong with the left join?

    Captain Guest

  3. #3

    Default Re: speedest way to handle a select table1,count(table2) where table1.idtable1=table2.idtable1 ?

    "Captain Paralytic" <com> a écrit dans le message de
    news:googlegroups.com... 
    > Why don't you like this?
    > Why would you group by table1.* when you are querying table1.x?[/ref]

    oops, i just this :
    select TABLE1.* ,count(TABLE2.IDT2) from TABLE1 left outer join TABLE2 using
    (IDT1) group by TABLE1.IDT1

    and it works
    i dont know why, but i was thinking that you need to use all select column
    in the group by


    patrice Guest

  4. #4

    Default Re: speedest way to handle a select table1,count(table2) where table1.idtable1=table2.idtable1 ?


    patrice <fr> wrote in
    <467119d4$0$29914$free.fr>: 
    >>
    >> Why don't you like this?
    >> Why would you group by table1.* when you are querying
    >> table1.x?[/ref]
    >
    > oops, i just this :
    > select TABLE1.* ,count(TABLE2.IDT2) from TABLE1 left outer
    > join TABLE2 using (IDT1) group by TABLE1.IDT1
    >
    > and it works
    > i dont know why, but i was thinking that you need to use
    > all select column in the group by[/ref]

    The following might be of interest to you:

    http://dev.mysql.com/tech-resources/articles/debunking-group-by-myths.html

    --
    Pavel Lepin
    Pavel Guest

  5. #5

    Default Re: speedest way to handle a select table1,count(table2) where table1.idtable1=table2.idtable1 ?

    On 14 Jun, 12:18, Pavel Lepin <com> wrote: [/ref]
    > [/ref]
    > [/ref]
    > [/ref]


    >
    > The following might be of interest to you:
    >
    > http://dev.mysql.com/tech-resources/articles/debunking-group-by-myths...
    >
    > --
    > Pavel Lepin- Hide quoted text -
    >
    > - Show quoted text -[/ref]

    But he was violating even the myth by wanting all the columns in the
    group by.

    Captain Guest

  6. #6

    Default Re: speedest way to handle a select table1,count(table2) where table1.idtable1=table2.idtable1 ?

    "Captain Paralytic" <com> a écrit dans le message de
    news:googlegroups.com... 
    > > 
    > > 
    > > 
    > > 
    > > 
    > >
    > > The following might be of interest to you:
    > >
    > > http://dev.mysql.com/tech-resources/articles/debunking-group-by-myths...
    > >
    > > --
    > > Pavel Lepin- Hide quoted text -
    > >
    > > - Show quoted text -[/ref]
    >
    > But he was violating even the myth by wanting all the columns in the
    > group by.
    >[/ref]

    i wanted all the column in the select clause, not the group clause (but IMO,
    i was forced to put them in the group one)



    patrice Guest

Similar Threads

  1. UPDATE table2 using table1 data
    By Trudge in forum MySQL
    Replies: 1
    Last Post: April 14th, 08:46 AM
  2. component mempry/handle count leak
    By Marcelo Ferreira in forum ASP Components
    Replies: 0
    Last Post: July 30th, 01:08 AM
  3. Replies: 2
    Last Post: March 8th, 11:57 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139