sql - PHP Mysql Joins and Where Clause

919

I need help on below statement. I need to putWHERE items.IID = 8 so that it shows only details pertaining to IID number 8. But when I useWHERE items.IID = 8, it is not working. I have to use this type of join as I want to do Sum and Count of some fields. There are 3 tables. 1st isitems, 2nd isItemPurchaseHistory and 3rd isItemIssuedHistory.

SELECT items.IID, items.ItemName, ItemPurchaseHistorySum.SumOfUnitsPurchased, ItemPurchaseHistorySum.SumOfCost,
ItemPurchaseHistoryCount.CountOfUnitsPurchased,
ItemIssuedHistorySum.SumOfUnitsIssued
FROM items

LEFT JOIN (SELECT IID, SUM(UnitsPurchased) AS SumOfUnitsPurchased, SUM(Cost) AS SumOfCost
FROM ItemPurchaseHistory
GROUP BY IID) ItemPurchaseHistorySum ON ItemPurchaseHistorySum.IID = items.IID

LEFT JOIN (SELECT IID, Count(UnitsPurchased) AS CountOfUnitsPurchased
FROM ItemPurchaseHistory
GROUP BY IID) ItemPurchaseHistoryCount ON ItemPurchaseHistoryCount.IID = items.IID

LEFT JOIN (SELECT IID, SUM(UnitsIssued) AS SumOfUnitsIssued
FROM ItemIssuedHistory
GROUP BY IID) ItemIssuedHistorySum ON ItemIssuedHistorySum.IID = items.IID

WHERE item.IID = $_GET['id']

ORDER BY items.IID ASC

People are also looking for solutions to the problem: javascript - Dynamic tfoot drop-down filters are missing certain options with server-side processing

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.