-2

I couldn't figure this out from existing kinda similar threads. I've tried left join and different sort of subqueries but no luck. I got left join work with group by but couldn't figure out how to add where clauses, then wen't to subqueries and broke everything.

I have two tables storage and orders.

Storage has list of unique id and name

    id   | name
    1234 | product1
    1235 | product2
    A123 | product3

Orders have multiple instances code, quantity and type

code | qty| type
1234 | 10 | order
1234 | 10 | quote
1234 | 10 | order
A123 | 15 | order
1235 | 13 | order

I wan't to join these tables so that I get filtered (with where) results from storage and join with summed qty where type is order.

For example filter storage where id in (1234, A123) should result:

id   | name     | sum qty    
1234 | product1 | 20
A123 | product3 | 15

Any help appreciated!

--

Going forward, storage has products and cols. There is table prod_to_col that has productid and col_id to tie them together. I would need to grab product codes from table prod_to_col and show total quantity for cols according to order quantity.

I tried this according to @iSR5 example:

SELECT st.id, st.name, SUM(order.qty) AS SumQty
FROM storage
JOIN prod_to_col ON st.id=prod_to_col.col_id 
JOIN orders ON order.id IN (SELECT prod_id FROM prod_to_col WHERE col_id=st.id) AND type='order'
WHERE id IN (1234, A123)
GROUP BY st.id, st.name

This almost works but quantities are multiplied in some rows some are fine, can someone point where it goes wrong?

In addition to tables storage and orders above, here's example of prod_to_col and cols:

Prod_to_col

prod_id | col_id | col_qty (per product)
1235    | C101   | 2
1236    | C102   | 1

Cols

col_id | name   | other data
C101   | cname1 | --
C102   | cname2 | --

Orders

prod_id | qty | type
1235    | 10  | order
1235    | 10  | order
1236    | 2   | quote
1236    | 5   | order

Storage

st.id | st.name| SumQty
C101  | cname1 | 40
C102  | cname2 |  5

I understand I need to use two different sentence to populate storage list, one for products and one for cols. The one for products works fine.

Rami R
  • 13
  • 6
  • 1
    `I've tried left join and different sort of subqueries but no luck.` <= [edit] your question and include the attempt that got you the closest and explain why it does not do what you want it to do. You get a lot further with other members on this site if you include your work up to this point. Otherwise the assumption is that you are wanting others to do your work for you. – Igor Sep 28 '18 at 19:48
  • I've expanded the question to contain subquery is same sentence. – Rami R Sep 28 '18 at 21:33
  • This seems like maybe it is the common error where one wants a join of separate join-group-aggregates. Which can sometimes be expressed by a sequence of joins then UNIQUE aggregation. [Strange duplicate behavior from GROUP_CONCAT of two LEFT JOINs of GROUP_BYs](https://stackoverflow.com/a/45252750/3404097) – philipxy Sep 28 '18 at 23:15
  • Please always google many clear, concise & specific versions/phrasings of your question/problem/goal with & without your particular strings/names & read many answers. Add relevant keywords you discover to your searches. If you don't find an answer then post, using 1 variant search as title & keywords for tags. See the downvote arrow mouseover text. When you do have a non-duplicate code question to post please read & act on [mcve]. – philipxy Sep 28 '18 at 23:16

2 Answers2

0

Try to use Left Join combined with a group by 

SELECT OD.CODE
      ,ST.PRODUCT
      ,SUM(quantity) as qnt
FROM ORDERS OD 
     LEFT JOIN STORAGE ST ON(OD.CODE = SG.CODE)
WHERE OD.type like 'order'
GROUP BY
      OD.CODE
      ,ST.PRODUCT

You can use Having to filter 

Having id in (1234, A123)

Greetings

Alvaro Parra
  • 796
  • 2
  • 8
  • 23
0

Is this is what you need ? 

DECLARE @Storage TABLE(ID VARCHAR(50), name VARCHAR(250) )
DECLARE @Orders TABLE(code VARCHAR(50), qty INT, type VARCHAR(50))

INSERT INTO @Storage VALUES
    ('1234','product1')
,   ('1235','product2')
,   ('A123','product3')

INSERT INTO @Orders VALUES 
    ('1234',10,'order')
,   ('1234',10,'quote')
,   ('1234',10,'order')
,   ('A123',15,'order')
,   ('1235',13,'order')

SELECT 
    s.ID 
,   s.name 
,   SUM(o.qty) TotalQty
FROM 
    @Storage s 
JOIN @Orders o ON o.code = s.ID AND o.type = 'order'
WHERE 
    s.ID IN('1234','A123')
GROUP BY 
    s.ID 
,   s.name

UPDATE

You've updated your post with more logic to cover, which wasn't provided before, however, I've update it the query for you .. 

DECLARE @Storage TABLE(ID VARCHAR(50), name VARCHAR(250) )
DECLARE @Orders TABLE(code VARCHAR(50), qty INT, type VARCHAR(50))
DECLARE @Prod_to_col TABLE(prod_id VARCHAR(50), col_id  VARCHAR(50), col_qty INT)
DECLARE @Cols TABLE(col_id VARCHAR(50), name  VARCHAR(250))


INSERT INTO @Storage VALUES
    ('1234','product1')
,   ('1235','product2')
,   ('A123','product3')
,   ('1236','product3')

INSERT INTO @Orders VALUES 
    ('1234',10,'order')
,   ('1234',10,'quote')
,   ('1234',10,'order')
,   ('A123',15,'order')
,   ('1235',10,'order')
,   ('1235',10,'order')
,   ('1236',2,'quote')
,   ('1236',5,'order')

INSERT INTO @Prod_to_col VALUES 
    ('1235','C101',2)
,   ('1236','C102',1)


INSERT INTO @Cols VALUES 
    ('C101','cname1')
,   ('C102','cname2')




SELECT 
    c.col_id 
,   c.name 
,   SUM(o.qty) * MAX(ptc.col_qty) TotalQty
FROM 
    @Storage s 
JOIN @Orders o ON o.code = s.ID AND o.type = 'order'
JOIN @Prod_to_col ptc ON ptc.prod_id = o.code
JOIN @Cols c ON c.col_id = ptc.col_id
--WHERE 
--    s.ID IN('1234','A123')
GROUP BY 
    c.col_id 
,   c.name
iSR5
  • 3,274
  • 2
  • 14
  • 13
  • So in this case the where clause is replaced with expanded declaration on join. My untrained eye says this works too but can't be sure. I think I'm gonna go with where as the actual case has more conditions than my simplified example. – Rami R Sep 28 '18 at 20:26
  • @rami if you use the filtering on the join directly, you're going to only join that set of records, but if you move it under WHERE, your join will grab everything in the other table that is matched with `code = id` then it'll filter that from the WHERE caluse. So, both will give you the same output, but the performance will be different specially in large tables. – iSR5 Sep 28 '18 at 20:31
  • Thanks for deeper explanation, appreciated. Tested and it does exactly what I need. I will proceed to next step and see which solution is easier to expand with couple more tables.. – Rami R Sep 28 '18 at 20:38
  • Could you please take a look on edited post with subquery? Thanks! – Rami R Sep 28 '18 at 21:37
  • @rami try this instead `JOIN orders ON order.id = prod_to_col.prod_id AND type='order'`, if didn't work, please provide the tables schema of storage, orders, and prod_to_col along with a sample of each. – iSR5 Sep 28 '18 at 21:54
  • Got empty table with this edit. I have added example data tables to op. – Rami R Sep 28 '18 at 22:17
  • With further research I found that my almost working tryout multiplies the sum with amount of instances (amount of products that share the same col in prod_to_col) – Rami R Sep 28 '18 at 22:37
  • @rami Please note how iSR5 has constructed most of a [mcve] for you. Give code like that in the future. Except you never gave a *clear specification* of how desired output is a function of input. (If you don't force yourself to do that, why think that you even know what you want?) (The answer starts with "Is this ....".) (Although an unclear question mertis comments not answers.) Use enough words & sentences to say what you mean. Also: Part of justifiying a MCVE is finding a working example for maximal partial functionality. Ask about the (small) difference between the examples. – philipxy Sep 28 '18 at 22:54
  • @iSR5 New day, fresh eyes; JOIN orders ON order.id = prod_to_col.prod_id AND type='order' did the the trick after all. Thank you very much. Sorry for not being able to post question proper enough to fullfill Stack Overflow requirements. Still have no idea what should be done different, maybe next time =) – Rami R Oct 01 '18 at 06:27