Joining two tables without miscalculated output or missing output with a NULL in table

Question

The first table calls stockcurrent and contains all product items from tree diffenrent stores. The secound table contains all sales in the tree stores. Now i want do join this two tables and get an output where i find to each sale the right stock in each store, it should look like the following table

sales Item store_1_stock store_2_stock store_3_stock But i always have two problems: The first: if one item was never in one of the tree stores it don´t appear in the listing The secound: if more than one item was sold in a given timeframe it doubled or trippled the output of the stock units

I need this query for adjustment between tree small comic stores. After my last questin i got here very good help so that i get the real stock units of all tree stores. But any try to join this result with the sales table bring me back to the two miscalculations of stock units.

this are pictures of the tables:

https://www.dropbox.com/preview/stockcurrent_table.PNG?role=personal

https://www.dropbox.com/preview/ticketlines_table.PNG?role=personal

SELECT 
products.name AS VK_Artikel,
SUM(CASE WHEN stockcurrent.location ='100' 
    THEN stockcurrent.units ELSE 0  END) AS Versand_Bestand,
SUM(CASE WHEN stockcurrent.location ='3b53adf5-eaee-4a13-b22b-39d50b14e497' 
    THEN stockcurrent.units ELSE 0 END) AS K_Strasse,
SUM(CASE WHEN stockcurrent.location ='a59cb899-27f4-460c-b5df-89a89eaaef75' 
    THEN stockcurrent.units ELSE 0 END) AS O_Strasse,
SUM(CASE WHEN stockcurrent.location =  '0' 
    THEN stockcurrent.units ELSE 0 END) AS EU
FROM ticketlines
  JOIN products
ON ticketlines.product = products.id
  JOIN tickets
ON ticketlines.ticket = tickets.id
  JOIN receipts 
ON tickets.id = receipts.id
  JOIN closedcash
ON receipts.money = closedcash.money
  JOIN stockcurrent
ON stockcurrent.product = products.id
  WHERE closedcash.host ='X_Online'
    AND closedcash.datestart > $P{startzeit}
Group BY VK_Artikel ASC    

I expect the follow output:

Sold Item  store_1_Stock store_2_Stock store_3_Stock
 comic 1         1              2            1

The Numbers are from each shop

I get if the item was sold more than once

Sold Item  store_1_Stock store_2_Stock store_3_Stock
 comic 1         3              6            3

or if one item was sold in one of the stores but never was available in all tree stores it never give an output.

Answer 1

To avoid miscalculation i suggest you do your calculation in separate sub clause and then join it with your main query.

SELECT 
    p.name AS VK_Artikel,
    s.Versand_Bestand,
    s.K_Strasse,
    s.O_Strasse,
    s.EU
FROM ticketlines tl
  JOIN products p
ON tl.product = p.id
  JOIN tickets t
ON tl.ticket = t.id
  JOIN receipts r
ON t.id = r.id
  JOIN closedcash c
ON r.money = c.money
 LEFT JOIN (
  SELECT product
    SUM(CASE WHEN location ='100' THEN units ELSE 0  END) AS Versand_Bestand,
    SUM(CASE WHEN location ='3b53adf5-eaee-4a13-b22b-39d50b14e497' THEN units ELSE 0 END) AS K_Strasse,
    SUM(CASE WHEN location ='a59cb899-27f4-460c-b5df-89a89eaaef75' THEN units ELSE 0 END) AS O_Strasse,
    SUM(CASE WHEN location =  '0' THEN units ELSE 0 END) AS EU
    FROM stockcurrent
    GROUP BY product
  ) s
ON s.product = p.id
  WHERE c.host ='X_Online'
    AND c.datestart > $P{startzeit}
ORDER BY VK_Artikel ASC 

There may be a chance that joins involved in query has many rows against each product which affects your numbers, Also if you still get duplicate rows you can add distinct in your select clause SELECT DISTINCT ....