MySQL VIEWS - Different results
Posted: Sat Jul 21, 2007 10:00 pm
Hi All,
Views are behaving weirdly for me. Can anyone see what I'm doing wrong?
Here's the view :
Here's the sql to call it (which is producing incorrect results) :
This outputs the correct output :
I can post output if it helps. But basically the view SQL is only outputting results that ONLY have a single entry.
Any thoughts?
tia, Will./
Views are behaving weirdly for me. Can anyone see what I'm doing wrong?
Here's the view :
Code: Select all
DROP VIEW returns;
CREATE VIEW returns AS
SELECT DISTINCT product_id,
SUM(amount) AS amount, return_date, price, returns_table.client_id, returns_table.date, returns_table.statement_id, returns_table.total
FROM return_items
LEFT JOIN returns_table
ON returns_id = returns_table.id
GROUP BY product_id;Code: Select all
select * from returns WHERE client_id = 5109;
Code: Select all
SELECT DISTINCT product_id, SUM(amount) AS amount, return_date, price, returns_table.client_id, returns_table.date, returns_table.statement_id, returns_table.total FROM return_items LEFT JOIN returns_table ON returns_id = returns_table.id WHERE client_id = 5109 GROUP BY product_id;
Any thoughts?
tia, Will./