Page 1 of 1

MySQL VIEWS - Different results

Posted: Sat Jul 21, 2007 10:00 pm
by facets
Hi All,

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;
Here's the sql to call it (which is producing incorrect results) :

Code: Select all

select * from returns WHERE client_id = 5109;
This outputs the correct output :

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;
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./

Posted: Sat Jul 21, 2007 10:22 pm
by facets
hmm, I removed DISTINCT in the create VIEW script and it works as expected. Not sure why yet.