How to use join syntax with group by for multiple tables

Question

I have 3 tables , adjustinlines , Items and itemmovement What i'm looking for is that i need to select from the tables : all the lines in the adjustinline with the description from the item table with the onhand quantity which is from the itemmovement table sum(qty)-sum(qtyout) group by itemid and warehouseid.

adjustinlines

itemid  warehouseid adjustbyquantity

1          2           5
1          1           3
2          2           4
3          1           5

Items

Id        description

1         Item 1
2         Item 2
3         Item 3

Itemmovement

itemid  warehouseid qtyin   qtyout

1          1         3  
1          1                  2
1          2         3  
1          2                  1
2          2         1

Request View

Description warehouseid Onhandqty   Adjustbyqty

Item 1         2          2             5
Item 1         1          1             3
Item 2         2          1             4
Item 3         1          0             5

I'm trying to use those 2 ways but both of them isn't giving me the correct result

SELECT sum(itemv.qtyin) sum(itemv.qtyout),adjo.warehouseid,adjo.itemid,
adjo.adjustbyquantity,itm.description

                        FROM adjustoutcopy adjo

                        LEFT JOIN  itemmovement AS itemv
                            ON  adjo.itemid=itemv.itemid AND 
                                adjo.warehouseid=itemv.warehouseid
                        LEFT JOIN  items AS itm
                            ON adjo.itemid=itm.Id



                        group by itemv.itemid,itemv.warehouseid 
                        ORDER BY adjo.Id DESC

If the itemid doesn't exist in the itemmovement table , the line isn't being appeared , as the code is using the join Inner .

Possible duplicate of [Strange duplicate behavior from GROUP\_CONCAT of two LEFT JOINs of GROUP\_BYs](https://stackoverflow.com/questions/45250646/strange-duplicate-behavior-from-group-concat-of-two-left-joins-of-group-bys) — philipxy, Nov 07 '18 at 11:43
This seems likely a faq where people need the join of multiple join+aggregates but they try to do the aggregations cumulatively. Basic debugging says, look at the result as you add more to your code & look at what is the input to the addition that causes unexpected results. Please read & act on [mcve]--cut & paste & runnable code & expected output & specification. Also if you googled many clear, concise, specific phrasings of your question/problem/goal with & without your names/strings you would find answers. — philipxy, Nov 07 '18 at 11:46

score 1 · Accepted Answer · answered Nov 08 '18 at 05:46

Finally it has been resolved , here is the code :

   SELECT itm.description,adjst.warehouseid,sumofqty,adjst.quantity

            FROM adjustoutcopy adjst
           LEFT JOIN (
              SELECT itemid,warehouseid,sum(qtyin)-sum(qtyout) AS sumofqty
              FROM itemmovement
              GROUP BY itemid,warehouseid
            ) s ON (adjst.codeid = s.itemid) AND
                    (adjst.warehouseid = s.warehouseid)
            LEFT JOIN items AS itm
                ON adjst.codeid=itm.Id

Thanks To all

You can (and should) accept your own answer to close this question. — Nick, Nov 19 '18 at 08:49

score 0 · Answer 2 · answered Nov 07 '18 at 14:49

0

Since you GROUP BY columns in the itemv table, if there is no entry in that table no group exists.

answered Nov 07 '18 at 14:49

Mike Robinson

8,490
5
28
41

How can i show the adjustinlines even if there wasn't entries in the itemv , and incase there is entires it will sum up the numbers that comply with the itemid and warehouseid ? – Moussa Nov 07 '18 at 15:59

How to use join syntax with group by for multiple tables

2 Answers2