-1

I may have messed up the joins or there may be another way of writing what I am trying to achieve.

My current query is this:

SELECT 
    i.SKI_NAME AS Description,
    l.SKV_QUANTITY_IN_STOCK AS Qty,
    SUM(CASE WHEN ad.AVN_STATUS = 'D' THEN li.AVL_QUANTITY ELSE '0' END) AS AdviceQty,
    SUM(CASE WHEN con.HCT_STATUS = 'D' THEN item.HIT_QUANTITY ELSE '0' END) AS HireQty
    
FROM
    TH_STOCK_LEVELS l
    
LEFT JOIN TH_STOCK_ITEMS i ON l.SKV_STOCK_NUMBER = i.SKI_STOCK_NUMBER
LEFT JOIN TH_ADVICE_NOTE_LINES li ON i.SKI_NAME = li.AVL_DESCRIPTION
LEFT JOIN TH_HIRE_ITEMS Item ON li.AVL_DESCRIPTION = Item.HIT_DESCRIPTION
LEFT JOIN TH_ADVICE_NOTES ad ON ad.AVN_ID = li.AVL_NOTE_NUMBER
LEFT JOIN TH_HIRE_CONTRACTS con ON con.HCT_CONTRACT_NUMBER = Item.HIT_CONTRACT_NUMBER
    
WHERE
    l.SKV_DEPOT_ID = 7
    
GROUP BY i.SKI_NAME, l.SKV_QUANTITY_IN_STOCK;

This displays the following output:

Description Qty AdviceQty HireQty
Some Item 2 400 100
Some Item 0 100 0

Which is incorrect, as it seems to be totalling all previous Advice's and Hire's and not just the ones with Status 'D'.

If I do the following to the query (comment some lines out):

SELECT 
    i.SKI_NAME AS Description,
    l.SKV_QUANTITY_IN_STOCK AS Qty,
    SUM(CASE WHEN ad.AVN_STATUS = 'D' THEN li.AVL_QUANTITY ELSE '0' END) AS AdviceQty
    --SUM(CASE WHEN con.HCT_STATUS = 'D' THEN item.HIT_QUANTITY ELSE '0' END) AS HireQty
    
FROM
    TH_STOCK_LEVELS l
    
LEFT JOIN TH_STOCK_ITEMS i ON l.SKV_STOCK_NUMBER = i.SKI_STOCK_NUMBER
LEFT JOIN TH_ADVICE_NOTE_LINES li ON i.SKI_NAME = li.AVL_DESCRIPTION
--LEFT JOIN TH_HIRE_ITEMS Item ON li.AVL_DESCRIPTION = Item.HIT_DESCRIPTION
LEFT JOIN TH_ADVICE_NOTES ad ON ad.AVN_ID = li.AVL_NOTE_NUMBER
--LEFT JOIN TH_HIRE_CONTRACTS con ON con.HCT_CONTRACT_NUMBER = Item.HIT_CONTRACT_NUMBER
    
WHERE
    l.SKV_DEPOT_ID = 7
    
GROUP BY i.SKI_NAME, l.SKV_QUANTITY_IN_STOCK;

The output is correct, although I am now missing a column due to the comments. This also works if I comment out the Advice tables, the HireQty column would be correct.

Description Qty AdviceQty
Some Item 2 10
Some Item 0 0

How do I get this to display the correct data for both AdviceQty & HireQty without having to do them separately?

philipxy
  • 14,867
  • 6
  • 39
  • 83
HPH WoodWorks
  • 1
  • 1
  • What this is telling me is that either TH_HIRE_ITEMS has 'many' rows for the same li.AVL_DESCRIPTION or TH_HIRE_CONTRACTS has 'many' rows for the same Item.HIT_CONTRACT_NUMBER (or both). Check those instances. – tinazmu Jun 01 '22 at 13:49
  • There would be many rows the same in both of these yes. Ideally, I wanted TH_HIRE_ITEMS to join on i_SKI_NAME as well. Essentially stock that get's added to a quote 'Advice where status = D' D is for quote unconfirmed here. Same with Hires. So I need to see my current stock 'Qty' with the unconfirmed stock of 'AdviceQty and 'HireQty' but I need to see all my stock so if no stock is in AdviceQty or HireQty it should return 0... I think I explained that horribly. – HPH WoodWorks Jun 01 '22 at 14:00
  • Essentially you can't sum the parent rows (TH_STOCK_LEVELS) when the join to a detail row would cause the same parent row to be returned many times (as in your case). Why do you need the join to those tables when you don't do anything with them (not in SELECT/WHERE/ or other JOINS)? – tinazmu Jun 01 '22 at 14:11
  • I thought this question would come up. So TH_HIRE_CONTRACTS contains the contract number that TH_HIRE_ITEMS relates to. TH_HIRE_CONTRACTS also tells me whether a contract is pending (unconfirmed) which is what I want. TH_HIRE_ITEMS contain the stock that is on the contract and the Qty to be sold when confirmed. All stock is in TH_STOCK_ITEMS but this table doesn't give me the Qty which is why TH_STOCK_LEVELS is needed. The Advice Note tables work the same way as the Hire Contracts. I don't need to see any other information other then what stock I have, the Qty available, and the Qty currently – HPH WoodWorks Jun 01 '22 at 14:21
  • Unconfirmed in either the Advice Notes and Hire Contracts. – HPH WoodWorks Jun 01 '22 at 14:22

1 Answers1

0

Sometimes, CASE statement doesn't work. You can try with IFs and see if it is working.

SELECT 
        i.SKI_NAME AS Description,
        l.SKV_QUANTITY_IN_STOCK AS Qty, SUM(IF(ad.AVN_STATUS='D',li.AVL_QUANTITY,0)) AS AdviceQty, SUM(IF(con.HCT_STATUS='D',item.HIT_QUANTITY,0)) AS HireQty
    
    
         FROM
        TH_STOCK_LEVELS l
         LEFT JOIN TH_STOCK_ITEMS i ON l.SKV_STOCK_NUMBER = i.SKI_STOCK_NUMBER LEFT JOIN TH_ADVICE_NOTE_LINES li ON i.SKI_NAME = li.AVL_DESCRIPTION LEFT JOIN TH_HIRE_ITEMS Item ON li.AVL_DESCRIPTION
    = Item.HIT_DESCRIPTION LEFT JOIN TH_ADVICE_NOTES ad ON ad.AVN_ID = li.AVL_NOTE_NUMBER LEFT JOIN TH_HIRE_CONTRACTS con ON con.HCT_CONTRACT_NUMBER = Item.HIT_CONTRACT_NUMBER
         WHERE
        l.SKV_DEPOT_ID = 7
         GROUP BY i.SKI_NAME, l.SKV_QUANTITY_IN_STOCK;
Zain Reza
  • 1
  • Thank you for your answer, unfortunately I have tried it with an IF but it just fails because of 'Incorrect syntax near the keyword 'IF'. – HPH WoodWorks Jun 01 '22 at 14:12
  • Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Jun 01 '22 at 16:13