Professional Web Applications Themes

Help query - MySQL

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 ...

  1. #1

    Default 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 ?


    Eugenio Guest

  2. #2

    Default Re: Help query

    On Jun 22, 8:52 am, "Eugenio Zinga" <it> wrote: 

    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

    strawberry Guest

  3. #3

    Default Re: Help query


    "strawberry" <com> ha scritto nel messaggio
    news:googlegroups.com... 
    >
    > 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
    >[/ref]
    Thanks for your answer. How you see the total of code 1 is mistaken like
    never?
    IDart TMOV1 TMOV2 


    Eugenio Guest

  4. #4

    Default Re: Help query

    strawberry help!!



    "Eugenio Zinga" <it> ha scritto nel messaggio
    news:467ba496$0$4791$news.tin.it... 
    >>
    >> 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
    >>[/ref]
    > Thanks for your answer. How you see the total of code 1 is mistaken like
    > never?
    > IDart TMOV1 TMOV2 
    >
    >[/ref]


    Eugenio Guest

  5. #5

    Default Re: Help query

    On Jun 25, 7:00 am, "Eugenio Zinga" <it> wrote: [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref][/ref]

    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

    strawberry Guest

  6. #6

    Default Re: Help query

    On Jun 25, 10:46 am, strawberry <com> wrote: 

    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    >
    > 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[/ref]

    I guess the real problem is the lack of a PRIMARY KEY on your MOV
    tables.

    strawberry Guest

  7. #7

    Default Re: Help query

    Not there is primary key in tables MOV.
    Infinite thanks.
    --------------
    "strawberry" <com> ha scritto nel messaggio
    news:googlegroups.com... 
    >> 
    >> 
    >> 
    >> 
    >> 
    >> 
    >> 
    >> 
    >> 
    >> 
    >> 
    >> 
    >> 
    >> 
    >> 
    >>
    >> 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[/ref]
    >
    > I guess the real problem is the lack of a PRIMARY KEY on your MOV
    > tables.
    >[/ref]


    Eugenio Guest

Similar Threads

  1. Query of Queries on query New type query
    By david_h in forum Coldfusion Database Access
    Replies: 2
    Last Post: May 6th, 08:55 PM
  2. Replies: 2
    Last Post: August 6th, 07:08 PM
  3. Replies: 1
    Last Post: July 2nd, 09:09 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