MySQL VIEWS - Different results

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
facets
Forum Contributor
Posts: 273
Joined: Wed Apr 13, 2005 1:53 am
Location: Detroit

MySQL VIEWS - Different results

Post 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./
facets
Forum Contributor
Posts: 273
Joined: Wed Apr 13, 2005 1:53 am
Location: Detroit

Post by facets »

hmm, I removed DISTINCT in the create VIEW script and it works as expected. Not sure why yet.
Post Reply