Help query
This is a discussion on Help query within the MySQL Database forums, part of the Database Forums category; In the first place excused my English. I do not succeed to understand as the result of the query with ...
|
|||||||
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
06-22-2007
|
|||
|
|||
Help query
In the first place excused my English.
I do not succeed to understand as the result of the query with 2 tables never is of the values mistakes to you while with 1 table the result is right. Thanks Eugene table ART: ------ IDart 01 02 03 table MOV1: ------- IDart qta 01 100 02 100 03 100 01 100 03 50 query1: SELECT IDart, SUM(MOV1.qta) as TMOV1 FROM ART LEFT JOIN MOV1 ON ART.IDart = MOV1.IDart GROUP BY ART.IDart result query OK: IDart TMOV1 01 200 02 100 03 150 ================================== table MOV2: ------- IDart qta 01 10 02 30 03 20 01 10 03 5 query2: SELECT IDart, SUM(MOV1.qta) as TMOV1, SUM(MOV2.qta) as TMOV2 FROM ART LEFT JOIN MOV1 ON ART.IDart = MOV1.IDart LEFT JOIN MOV2 ON ART.IDart = MOV2.IDart GROUP BY ART.IDart result query NO OK: IDart TMOV1 TMOV2 01 8210 ? 10000 ? 02 3200 ? 9300 ? 03 1720 ? 4000 ? 
|
|
06-22-2007
|
|||
|
|||
|
Re: Help query
On Jun 22, 8:52 am, "Eugenio Zinga" <eugzi...@tin.it> wrote:
> In the first place excused my English. > I do not succeed to understand as the result of the query with 2 tables > never is of the values mistakes to you while with 1 table the result is > right. > Thanks Eugene > > table ART: > ------ > IDart > 01 > 02 > 03 > > table MOV1: > ------- > IDart qta > 01 100 > 02 100 > 03 100 > 01 100 > 03 50 > > query1: > SELECT IDart, SUM(MOV1.qta) as TMOV1 > FROM ART > LEFT JOIN MOV1 ON ART.IDart = MOV1.IDart > GROUP BY ART.IDart > > result query OK: > IDart TMOV1 > 01 200 > 02 100 > 03 150 > > ================================== > > table MOV2: > ------- > IDart qta > 01 10 > 02 30 > 03 20 > 01 10 > 03 5 > > query2: > SELECT IDart, SUM(MOV1.qta) as TMOV1, SUM(MOV2.qta) as TMOV2 > FROM ART > LEFT JOIN MOV1 ON ART.IDart = MOV1.IDart > LEFT JOIN MOV2 ON ART.IDart = MOV2.IDart > GROUP BY ART.IDart > > result query NO OK: > IDart TMOV1 TMOV2 > 01 8210 ? 10000 ? > 02 3200 ? 9300 ? > 03 1720 ? 4000 ? Query: SELECT ART.IDart, SUM(MOV1.qta) as TMOV1, SUM(MOV2.qta) as TMOV2 FROM ART LEFT JOIN MOV1 ON ART.IDart = MOV1.IDart LEFT JOIN MOV2 ON ART.IDart = MOV2.IDart GROUP BY ART.IDart Result: IDart TMOV1 TMOV2 1 400 40 2 100 30 3 300 50
|
|
06-22-2007
|
|||
|
|||
|
Re: Help query
"strawberry" <zac.carey@gmail.com> ha scritto nel messaggio news:1182505502.442836.295500@c77g2000hse.googlegr oups.com... > On Jun 22, 8:52 am, "Eugenio Zinga" <eugzi...@tin.it> wrote: >> In the first place excused my English. >> I do not succeed to understand as the result of the query with 2 tables >> never is of the values mistakes to you while with 1 table the result is >> right. >> Thanks Eugene >> >> table ART: >> ------ >> IDart >> 01 >> 02 >> 03 >> >> table MOV1: >> ------- >> IDart qta >> 01 100 >> 02 100 >> 03 100 >> 01 100 >> 03 50 >> >> query1: >> SELECT IDart, SUM(MOV1.qta) as TMOV1 >> FROM ART >> LEFT JOIN MOV1 ON ART.IDart = MOV1.IDart >> GROUP BY ART.IDart >> >> result query OK: >> IDart TMOV1 >> 01 200 >> 02 100 >> 03 150 >> >> ================================== >> >> table MOV2: >> ------- >> IDart qta >> 01 10 >> 02 30 >> 03 20 >> 01 10 >> 03 5 >> >> query2: >> SELECT IDart, SUM(MOV1.qta) as TMOV1, SUM(MOV2.qta) as TMOV2 >> FROM ART >> LEFT JOIN MOV1 ON ART.IDart = MOV1.IDart >> LEFT JOIN MOV2 ON ART.IDart = MOV2.IDart >> GROUP BY ART.IDart >> >> result query NO OK: >> IDart TMOV1 TMOV2 >> 01 8210 ? 10000 ? >> 02 3200 ? 9300 ? >> 03 1720 ? 4000 ? > > Query: > > SELECT ART.IDart, SUM(MOV1.qta) as TMOV1, SUM(MOV2.qta) as TMOV2 > FROM ART > LEFT JOIN MOV1 ON ART.IDart = MOV1.IDart > LEFT JOIN MOV2 ON ART.IDart = MOV2.IDart > GROUP BY ART.IDart > > Result: > > IDart TMOV1 TMOV2 > 1 400?? 40 ?? > 2 100 30 > 3 300 50 > Thanks for your answer. How you see the total of code 1 is mistaken like never? IDart TMOV1 TMOV2 > 1 200 20 Ok > 2 100 30 > 3 300 50
|
|
06-25-2007
|
|||
|
|||
|
Re: Help query
strawberry help!!
"Eugenio Zinga" <eugzinga@tin.it> ha scritto nel messaggio news:467ba496$0$4791$4fafbaef@reader4.news.tin.it. .. > > "strawberry" <zac.carey@gmail.com> ha scritto nel messaggio > news:1182505502.442836.295500@c77g2000hse.googlegr oups.com... >> On Jun 22, 8:52 am, "Eugenio Zinga" <eugzi...@tin.it> wrote: >>> In the first place excused my English. >>> I do not succeed to understand as the result of the query with 2 tables >>> never is of the values mistakes to you while with 1 table the result is >>> right. >>> Thanks Eugene >>> >>> table ART: >>> ------ >>> IDart >>> 01 >>> 02 >>> 03 >>> >>> table MOV1: >>> ------- >>> IDart qta >>> 01 100 >>> 02 100 >>> 03 100 >>> 01 100 >>> 03 50 >>> >>> query1: >>> SELECT IDart, SUM(MOV1.qta) as TMOV1 >>> FROM ART >>> LEFT JOIN MOV1 ON ART.IDart = MOV1.IDart >>> GROUP BY ART.IDart >>> >>> result query OK: >>> IDart TMOV1 >>> 01 200 >>> 02 100 >>> 03 150 >>> >>> ================================== >>> >>> table MOV2: >>> ------- >>> IDart qta >>> 01 10 >>> 02 30 >>> 03 20 >>> 01 10 >>> 03 5 >>> >>> query2: >>> SELECT IDart, SUM(MOV1.qta) as TMOV1, SUM(MOV2.qta) as TMOV2 >>> FROM ART >>> LEFT JOIN MOV1 ON ART.IDart = MOV1.IDart >>> LEFT JOIN MOV2 ON ART.IDart = MOV2.IDart >>> GROUP BY ART.IDart >>> >>> result query NO OK: >>> IDart TMOV1 TMOV2 >>> 01 8210 ? 10000 ? >>> 02 3200 ? 9300 ? >>> 03 1720 ? 4000 ? >> >> Query: >> >> SELECT ART.IDart, SUM(MOV1.qta) as TMOV1, SUM(MOV2.qta) as TMOV2 >> FROM ART >> LEFT JOIN MOV1 ON ART.IDart = MOV1.IDart >> LEFT JOIN MOV2 ON ART.IDart = MOV2.IDart >> GROUP BY ART.IDart >> >> Result: >> >> IDart TMOV1 TMOV2 >> 1 400?? 40 ?? >> 2 100 30 >> 3 300 50 >> > Thanks for your answer. How you see the total of code 1 is mistaken like > never? > IDart TMOV1 TMOV2 >> 1 200 20 Ok >> 2 100 30 >> 3 300 50 > >
|
|
06-25-2007
|
|||
|
|||
|
Re: Help query
On Jun 25, 7:00 am, "Eugenio Zinga" <eugzi...@tin.it> wrote:
> strawberry help!! > > "Eugenio Zinga" <eugzi...@tin.it> ha scritto nel messaggionews:467ba496$0$4791$4fafbaef@reader4.new s.tin.it... > > > > > "strawberry" <zac.ca...@gmail.com> ha scritto nel messaggio > >news:1182505502.442836.295500@c77g2000hse.googleg roups.com... > >> On Jun 22, 8:52 am, "Eugenio Zinga" <eugzi...@tin.it> wrote: > >>> In the first place excused my English. > >>> I do not succeed to understand as the result of the query with 2 tables > >>> never is of the values mistakes to you while with 1 table the result is > >>> right. > >>> Thanks Eugene > > >>> table ART: > >>> ------ > >>> IDart > >>> 01 > >>> 02 > >>> 03 > > >>> table MOV1: > >>> ------- > >>> IDart qta > >>> 01 100 > >>> 02 100 > >>> 03 100 > >>> 01 100 > >>> 03 50 > > >>> query1: > >>> SELECT IDart, SUM(MOV1.qta) as TMOV1 > >>> FROM ART > >>> LEFT JOIN MOV1 ON ART.IDart = MOV1.IDart > >>> GROUP BY ART.IDart > > >>> result query OK: > >>> IDart TMOV1 > >>> 01 200 > >>> 02 100 > >>> 03 150 > > >>> ================================== > > >>> table MOV2: > >>> ------- > >>> IDart qta > >>> 01 10 > >>> 02 30 > >>> 03 20 > >>> 01 10 > >>> 03 5 > > >>> query2: > >>> SELECT IDart, SUM(MOV1.qta) as TMOV1, SUM(MOV2.qta) as TMOV2 > >>> FROM ART > >>> LEFT JOIN MOV1 ON ART.IDart = MOV1.IDart > >>> LEFT JOIN MOV2 ON ART.IDart = MOV2.IDart > >>> GROUP BY ART.IDart > > >>> result query NO OK: > >>> IDart TMOV1 TMOV2 > >>> 01 8210 ? 10000 ? > >>> 02 3200 ? 9300 ? > >>> 03 1720 ? 4000 ? > > >> Query: > > >> SELECT ART.IDart, SUM(MOV1.qta) as TMOV1, SUM(MOV2.qta) as TMOV2 > >> FROM ART > >> LEFT JOIN MOV1 ON ART.IDart = MOV1.IDart > >> LEFT JOIN MOV2 ON ART.IDart = MOV2.IDart > >> GROUP BY ART.IDart > > >> Result: > > >> IDart TMOV1 TMOV2 > >> 1 400?? 40 ?? > >> 2 100 30 > >> 3 300 50 > > > Thanks for your answer. How you see the total of code 1 is mistaken like > > never? > > IDart TMOV1 TMOV2 > >> 1 200 20 Ok > >> 2 100 30 > >> 3 300 50 Well here's one way: SELECT a.art_id,x.TMOV1,y.TMOV2 FROM art a LEFT JOIN (SELECT art.art_id, SUM(MOV1.qta) as TMOV1 FROM ART LEFT JOIN MOV1 ON ART.art_id = MOV1.art_id GROUP BY ART.art_id)x ON a.art_id = x.art_id LEFT JOIN (SELECT art.art_id, SUM(MOV2.qta) as TMOV2 FROM ART LEFT JOIN MOV2 ON ART.art_id = MOV2.art_id GROUP BY ART.art_id)y ON a.art_id = y.art_id
|
|
06-25-2007
|
|||
|
|||
|
Re: Help query
On Jun 25, 10:46 am, strawberry <zac.ca...@gmail.com> wrote:
> On Jun 25, 7:00 am, "Eugenio Zinga" <eugzi...@tin.it> wrote: > > > > > strawberry help!! > > > "Eugenio Zinga" <eugzi...@tin.it> ha scritto nel messaggionews:467ba496$0$4791$4fafbaef@reader4.new s.tin.it... > > > > "strawberry" <zac.ca...@gmail.com> ha scritto nel messaggio > > >news:1182505502.442836.295500@c77g2000hse.googleg roups.com... > > >> On Jun 22, 8:52 am, "Eugenio Zinga" <eugzi...@tin.it> wrote: > > >>> In the first place excused my English. > > >>> I do not succeed to understand as the result of the query with 2 tables > > >>> never is of the values mistakes to you while with 1 table the result is > > >>> right. > > >>> Thanks Eugene > > > >>> table ART: > > >>> ------ > > >>> IDart > > >>> 01 > > >>> 02 > > >>> 03 > > > >>> table MOV1: > > >>> ------- > > >>> IDart qta > > >>> 01 100 > > >>> 02 100 > > >>> 03 100 > > >>> 01 100 > > >>> 03 50 > > > >>> query1: > > >>> SELECT IDart, SUM(MOV1.qta) as TMOV1 > > >>> FROM ART > > >>> LEFT JOIN MOV1 ON ART.IDart = MOV1.IDart > > >>> GROUP BY ART.IDart > > > >>> result query OK: > > >>> IDart TMOV1 > > >>> 01 200 > > >>> 02 100 > > >>> 03 150 > > > >>> ================================== > > > >>> table MOV2: > > >>> ------- > > >>> IDart qta > > >>> 01 10 > > >>> 02 30 > > >>> 03 20 > > >>> 01 10 > > >>> 03 5 > > > >>> query2: > > >>> SELECT IDart, SUM(MOV1.qta) as TMOV1, SUM(MOV2.qta) as TMOV2 > > >>> FROM ART > > >>> LEFT JOIN MOV1 ON ART.IDart = MOV1.IDart > > >>> LEFT JOIN MOV2 ON ART.IDart = MOV2.IDart > > >>> GROUP BY ART.IDart > > > >>> result query NO OK: > > >>> IDart TMOV1 TMOV2 > > >>> 01 8210 ? 10000 ? > > >>> 02 3200 ? 9300 ? > > >>> 03 1720 ? 4000 ? > > > >> Query: > > > >> SELECT ART.IDart, SUM(MOV1.qta) as TMOV1, SUM(MOV2.qta) as TMOV2 > > >> FROM ART > > >> LEFT JOIN MOV1 ON ART.IDart = MOV1.IDart > > >> LEFT JOIN MOV2 ON ART.IDart = MOV2.IDart > > >> GROUP BY ART.IDart > > > >> Result: > > > >> IDart TMOV1 TMOV2 > > >> 1 400?? 40 ?? > > >> 2 100 30 > > >> 3 300 50 > > > > Thanks for your answer. How you see the total of code 1 is mistaken like > > > never? > > > IDart TMOV1 TMOV2 > > >> 1 200 20 Ok > > >> 2 100 30 > > >> 3 300 50 > > Well here's one way: > > SELECT a.art_id,x.TMOV1,y.TMOV2 FROM art a > LEFT JOIN > (SELECT art.art_id, SUM(MOV1.qta) as TMOV1 > FROM ART > LEFT JOIN MOV1 ON ART.art_id = MOV1.art_id > GROUP BY ART.art_id)x > ON a.art_id = x.art_id > LEFT JOIN > (SELECT art.art_id, SUM(MOV2.qta) as TMOV2 > FROM ART > LEFT JOIN MOV2 ON ART.art_id = MOV2.art_id > GROUP BY ART.art_id)y > ON a.art_id = y.art_id I guess the real problem is the lack of a PRIMARY KEY on your MOV tables.
|
|
06-25-2007
|
|||
|
|||
|
Re: Help query
Not there is primary key in tables MOV.
Infinite thanks. -------------- "strawberry" <zac.carey@gmail.com> ha scritto nel messaggio news:1182766326.722684.304870@p77g2000hsh.googlegr oups.com... > On Jun 25, 10:46 am, strawberry <zac.ca...@gmail.com> wrote: >> On Jun 25, 7:00 am, "Eugenio Zinga" <eugzi...@tin.it> wrote: >> >> >> >> > strawberry help!! >> >> > "Eugenio Zinga" <eugzi...@tin.it> ha scritto nel >> > messaggionews:467ba496$0$4791$4fafbaef@reader4.new s.tin.it... >> >> > > "strawberry" <zac.ca...@gmail.com> ha scritto nel messaggio >> > >news:1182505502.442836.295500@c77g2000hse.googleg roups.com... >> > >> On Jun 22, 8:52 am, "Eugenio Zinga" <eugzi...@tin.it> wrote: >> > >>> In the first place excused my English. >> > >>> I do not succeed to understand as the result of the query with 2 >> > >>> tables >> > >>> never is of the values mistakes to you while with 1 table the >> > >>> result is >> > >>> right. >> > >>> Thanks Eugene >> >> > >>> table ART: >> > >>> ------ >> > >>> IDart >> > >>> 01 >> > >>> 02 >> > >>> 03 >> >> > >>> table MOV1: >> > >>> ------- >> > >>> IDart qta >> > >>> 01 100 >> > >>> 02 100 >> > >>> 03 100 >> > >>> 01 100 >> > >>> 03 50 >> >> > >>> query1: >> > >>> SELECT IDart, SUM(MOV1.qta) as TMOV1 >> > >>> FROM ART >> > >>> LEFT JOIN MOV1 ON ART.IDart = MOV1.IDart >> > >>> GROUP BY ART.IDart >> >> > >>> result query OK: >> > >>> IDart TMOV1 >> > >>> 01 200 >> > >>> 02 100 >> > >>> 03 150 >> >> > >>> ================================== >> >> > >>> table MOV2: >> > >>> ------- >> > >>> IDart qta >> > >>> 01 10 >> > >>> 02 30 >> > >>> 03 20 >> > >>> 01 10 >> > >>> 03 5 >> >> > >>> query2: >> > >>> SELECT IDart, SUM(MOV1.qta) as TMOV1, SUM(MOV2.qta) as TMOV2 >> > >>> FROM ART >> > >>> LEFT JOIN MOV1 ON ART.IDart = MOV1.IDart >> > >>> LEFT JOIN MOV2 ON ART.IDart = MOV2.IDart >> > >>> GROUP BY ART.IDart >> >> > >>> result query NO OK: >> > >>> IDart TMOV1 TMOV2 >> > >>> 01 8210 ? 10000 ? >> > >>> 02 3200 ? 9300 ? >> > >>> 03 1720 ? 4000 ? >> >> > >> Query: >> >> > >> SELECT ART.IDart, SUM(MOV1.qta) as TMOV1, SUM(MOV2.qta) as TMOV2 >> > >> FROM ART >> > >> LEFT JOIN MOV1 ON ART.IDart = MOV1.IDart >> > >> LEFT JOIN MOV2 ON ART.IDart = MOV2.IDart >> > >> GROUP BY ART.IDart >> >> > >> Result: >> >> > >> IDart TMOV1 TMOV2 >> > >> 1 400?? 40 ?? >> > >> 2 100 30 >> > >> 3 300 50 >> >> > > Thanks for your answer. How you see the total of code 1 is mistaken >> > > like >> > > never? >> > > IDart TMOV1 TMOV2 >> > >> 1 200 20 Ok >> > >> 2 100 30 >> > >> 3 300 50 >> >> Well here's one way: >> >> SELECT a.art_id,x.TMOV1,y.TMOV2 FROM art a >> LEFT JOIN >> (SELECT art.art_id, SUM(MOV1.qta) as TMOV1 >> FROM ART >> LEFT JOIN MOV1 ON ART.art_id = MOV1.art_id >> GROUP BY ART.art_id)x >> ON a.art_id = x.art_id >> LEFT JOIN >> (SELECT art.art_id, SUM(MOV2.qta) as TMOV2 >> FROM ART >> LEFT JOIN MOV2 ON ART.art_id = MOV2.art_id >> GROUP BY ART.art_id)y >> ON a.art_id = y.art_id > > I guess the real problem is the lack of a PRIMARY KEY on your MOV > tables. >
|
Linear Mode
