![]() |
|
Welcome to the Computer Webmaster Gaming Console Graphics Forum forums. You are currently viewing our boards as a guest which gives you limited access to view most discussions and access our other features. By joining our free community you will have access to post topics, communicate privately with other members (PM), respond to polls, upload content and access many other special features. Registration is fast, simple and absolutely free so please, join our community today! If you have any problems with the registration process or your account login, please contact contact us. |
| |||||||
| Database Database problems or need to ask a question? maybe something to do with sql injections or a database software question. Database topics cover MySQL, PostgreSQL, Oracle, SQL Server or anything else related to databases. |
![]() |
| | LinkBack | Thread Tools | Display Modes |
| | #1 | ||
| 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 ? | |||
| Advertisements |
| | #2 | ||
| 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 | |||
| | #3 | ||
| "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 | |||
| | #4 | ||
| 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 > > | |||
| | #5 | ||
| 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 | |||
| | #6 | ||
| 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. | |||
| | #7 | ||
| 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. > | |||
| Featured Websites | ||||
|
![]() |
| Tags: help, query |
| Currently Active Users Viewing This Thread: 1 (0 members and 1 guests) | |
| Thread Tools | |
| Display Modes | |
| |
Similar Threads | ||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| info query | Eugenio Zinga | Database | 2 | 07-01-2007 11:02 PM |
| ***Sql Query problem | Thomas 'PointedEars' Lahn | Database | 0 | 07-01-2007 6:36 PM |
| simple (?) query--help | Bosconian | Database | 2 | 07-01-2007 6:35 PM |
| Re-using query result | Nico Schuyt | PHP | 3 | 07-01-2007 4:18 PM |
| help with query | Alex Stuy | Database | 4 | 05-31-2007 8:45 PM |
| Featured Websites | ||||
|