I have a complex query which I can't quite get right.
I have a table of lists which is the master table for listItems and a related items table. A list contains a group of items, e.g. listId 1 has two items IDs 1 and 5. The listItems table contains a list of all items in each list. The items table contains information about the items.
The 'transactions' table is a list of items being added or removed (quantity) at a locationId.
I want to look up the stock for each item in each list at each location. Currently I have the following query:
SELECT
transactions.locationId as locationId,
lists.id as listId,
lists.name AS listName,
listitems.itemId,
listitems.quantity,
items.name,
CAST(SUM(transactions.quantity) AS INT) AS currentStock,
items.warningLevel
FROM lists
LEFT JOIN listItems ON lists.id = listitems.listId
LEFT JOIN items ON items.id = listitems.itemId
LEFT JOIN transactions ON transactions.itemId = listitems.itemId
WHERE lists.organisationId = 1 AND lists.deleted = 0 AND transactions.organisationId = 1
GROUP BY transactions.locationId, lists.id, items.id;
Which returns:
| locationId | listId | listName | itemId | quantity | name | currentStock | warningLevel | |
|---|---|---|---|---|---|---|---|---|
| 1 | 1 | ECG/TPC | 1 | 5 | Tympanic Probe Covers | 10 | 5 | |
| 1 | 1 | ECG/TPC | 5 | 2 | ECG Dots | 5 | 10 | |
| 1 | 2 | Cannulas 20 and 22 | 6 | 4 | Cannula - 20G | 10 | 20 | |
| 1 | 2 | Cannulas 20 and 22 | 7 | 1 | Cannula - 22G | 1 | 20 | |
| 4 | 1 | ECG/TPC | 1 | 5 | Tympanic Probe Covers | 2 | 5 | |
| 4 | 1 | ECG/TPC | 5 | 2 | ECG Dots | 8 | 10 | |
| 6 | 1 | ECG/TPC | 5 | 2 | ECG Dots | 0 | 10 | |
| 10 | 1 | ECG/TPC | 5 | 2 | ECG Dots | 5 | 10 |
The last two rows also need the rest of the items in the list (listId = 1), so it produces the following (emphasis mine):
| locationId | listId | listName | itemId | quantity | name | currentStock | warningLevel |
|---|---|---|---|---|---|---|---|
| 1 | 1 | ECG/TPC | 1 | 5 | Tympanic Probe Covers | 10 | 5 |
| 1 | 1 | ECG/TPC | 5 | 2 | ECG Dots | 5 | 10 |
| 1 | 2 | Cannulas 20 and 22 | 6 | 4 | Cannula - 20G | 10 | 20 |
| 1 | 2 | Cannulas 20 and 22 | 7 | 1 | Cannula - 22G | 1 | 20 |
| 4 | 1 | ECG/TPC | 1 | 5 | Tympanic Probe Covers | 2 | 5 |
| 4 | 1 | ECG/TPC | 5 | 2 | ECG Dots | 8 | 10 |
| 6 | 1 | ECG/TPC | 1 | 5 | Tympanic Probe Covers | NULL | 5 |
| 6 | 1 | ECG/TPC | 5 | 2 | ECG Dots | 0 | 10 |
| 10 | 1 | ECG/TPC | 5 | 2 | ECG Dots | 5 | 10 |
| 10 | 1 | ECG/TPC | 5 | 2 | ECG Dots | NULL | 10 |
Note: I've put "NULL" in currentStock for the desired outcome to highlight that this data doesn't exist (there's no transactions for that itemId at the locationId), ideally it would just say 0 instead of NULL.
Currently this fails because there are no entries in transactions with itemId of 1 at locationId 6, or 5 at 10.
I feel like I'm close but I can't think of a way to get the result I need - any help would be really appreciated.
P.S. I have tried searching for answers but I'm not even sure how to phrase the question, hence the terrible title
