I've been struggling with this for a couple of hours now, so I felt it was time to post!
I'm self learning SQL over the years, and have a grasp of the basics, but it gets to a bit of trial and error (sometimes mostly error!) when it comes to advanced aspects!
I have three tables, customers, orders and transactions.
I want to return a report of customers where the total order value and total transaction value do not match - e.g. they have returned an item, but have not been given their refund.
This is the first query I tried, which didn't work since, as I learnt this morning, I can't use aggregate functions in the where clause.
SELECT cId, SUM(oTotal) AS vordttl, SUM(tValue) AS vtranttl
FROM (Customers LEFT JOIN Orders ON Customers.cId = Orders.oCust) LEFT JOIN Transactions ON Customers.cId = Transactions.tCust
WHERE SUM(oTotal) <> SUM(tValue)
GROUP BY cId
Since then I've tried a whole host of ways round, which haven't worked...I've chosen to show the option above as whlist I know it's wrong, to human logic I think it expresses most clearly what I am trying to do.
Can someone help me achieve the result I'm looking for?