I have managed to do this report query. The good news is that it works but the returned query ignores all the other books which do not have sales or gift records for the previous months(10). What I need is for it to show records for all books in the table irrespective of previous month sales or gifted.Any help will be much appreciated
SELECT
sum(s1.bk_qty) AS csale,
sum(s2.bk_qty) AS lsale,
sum(g1.gQty) AS cgift,
sum(g2.gQty) AS lgift,
book.bk_Title,
book.bk_sellprice,
remark.remark,
SUM(stock.sto_qty) AS cstock,
book.bk_loc,
`language`.lan_name
FROM
sale AS s1 ,
sale AS s2 ,
gift AS g1 ,
gift AS g2 ,
book ,
remark ,
stock ,
`language`
WHERE
s1.s_year = '2010' AND
s1.s_month = '11' AND
s2.s_year = '2010' AND
s2.s_month = '10' AND
g1.gMonth = '11' AND
g2.gYear = '2010' AND
g1.gYear = '2010' AND
g2.gMonth = '10' AND
remark.r_year = '2010' AND
remark.r_month = '11'AND
book.bk_Id = s1.bk_id AND
book.bk_Id = s2.bk_id AND
book.bk_Id = remark.bk_Id AND
book.bk_Id=g1.bk_id AND
book.bk_Id=g2.bk_id AND
book.bk_Id=stock.bk_Id AND
book.bk_language=language.lan_id
GROUP BY
book.bk_Id,
s1.bk_id,
g1.bk_id,
remark.bk_Id,
g2.bk_id,
s2.bk_id,
stock.bk_Id
ORDER BY
book.bk_Id ASC