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 ?

On 14 Jun, 09:36, "patrice" <patrice_labracherie_nos...@free.fr>
wrote:
> 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.* "
Why don't you like this?
Why would you group by table1.* when you are querying table1.x?

> 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)
Why is this any better than a LEFT JOIN?

> so is there a rewriting of this imaginary query that can produce the desired
> column ?
Why do you want to re-write? What uis wrong with the left join?

"Captain Paralytic" <paul_lautman@yahoo.com> a écrit dans le message de
news:1181810746.693039.245080@z28g2000prd.googlegr oups.com...
> On 14 Jun, 09:36, "patrice" <patrice_labracherie_nos...@free.fr>
> wrote:
> > 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.* "
> Why don't you like this?
> Why would you group by table1.* when you are querying table1.x?

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 <patrice_labracherie_nospam@free.fr> wrote in
<467119d4$0$29914$426a34cc@news.free.fr>:
> "Captain Paralytic" <paul_lautman@yahoo.com> a écrit
>> On 14 Jun, 09:36, "patrice"
>> <patrice_labracherie_nos...@free.fr> wrote:
>> > 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.* "
>>
>> Why don't you like this?
>> Why would you group by table1.* when you are querying
>> table1.x?
>
> 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

The following might be of interest to you:

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

--
Pavel Lepin

On 14 Jun, 12:18, Pavel Lepin <p.le...@ctncorp.com> wrote:
> patrice <patrice_labracherie_nos...@free.fr> wrote in
> <467119d4$0$29914$426a3...@news.free.fr>:
>
>
>
>
>
> > "Captain Paralytic" <paul_laut...@yahoo.com> a ?crit
> >> On 14 Jun, 09:36, "patrice"
> >> <patrice_labracherie_nos...@free.fr> wrote:
> >> > 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.* "
>
> >> Why don't you like this?
> >> Why would you group by table1.* when you are querying
> >> table1.x?
>
> > 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
>
> The following might be of interest to you:
>
> http://dev.mysql.com/tech-resources/...group-by-myths...
>
> --
> Pavel Lepin- Hide quoted text -
>
> - Show quoted text -

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

"Captain Paralytic" <paul_lautman@yahoo.com> a écrit dans le message de
news:1181820455.752483.208770@e9g2000prf.googlegro ups.com...
> On 14 Jun, 12:18, Pavel Lepin <p.le...@ctncorp.com> wrote:
> > patrice <patrice_labracherie_nos...@free.fr> wrote in
> > <467119d4$0$29914$426a3...@news.free.fr>:
> >
> >
> >
> >
> >
> > > "Captain Paralytic" <paul_laut...@yahoo.com> a ?crit
> > >> On 14 Jun, 09:36, "patrice"
> > >> <patrice_labracherie_nos...@free.fr> wrote:
> > >> > 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.* "
> >
> > >> Why don't you like this?
> > >> Why would you group by table1.* when you are querying
> > >> table1.x?
> >
> > > 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
> >
> > The following might be of interest to you:
> >
> > http://dev.mysql.com/tech-resources/...group-by-myths...
> >
> > --
> > Pavel Lepin- Hide quoted text -
> >
> > - Show quoted text -
>
> But he was violating even the myth by wanting all the columns in the
> group by.
>

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)