My sql returns the first row each time,

Question

SELECT p.Producttype, pp.ProductID, r.Date1, r.Date2
FROM customers AS c
INNER JOIN orders AS r ON c.Cusid = r.Cusid
INNER JOIN temporary AS temp ON r.Cusid = temp.Cusid
INNER JOIN products AS pp ON pp.ProductID = temp.ProductID
INNER JOIN Producttypes AS p ON p.Producttypeid = pp.Producttypeid
WHERE temp.Cusid =  '23'
GROUP BY pp.ProductID

Help please i do not know what else to do, the query returns every time the first row into columns date1 and date2

Can you show a sample of your table. I don't understand what your question is — usumoio, Apr 25 '13 at 00:17
sorry i'm new in here, the problem is that each time the query returns the first row in all the rows, all the rows in the date columns have the same value — marina, Apr 25 '13 at 00:19

score 0 · Answer 1 · edited May 23 '17 at 12:12

This is a known issue of MySql. It will allow to group by without any aggregate function of non-grouped fields.

it returns the first aggregate for all fields outside the group-by in its default behavior. see this: Why does MySQL allow "group by" queries WITHOUT aggregate functions? and Any reason for GROUP BY clause without aggregation function?

remove the Group By clause and you should be fine.

SELECT p.Producttype, pp.ProductID, r.Date1, r.Date2
FROM customers AS c
INNER JOIN orders AS r ON c.Cusid = r.Cusid
INNER JOIN temporary AS temp ON r.Cusid = temp.Cusid
INNER JOIN products AS pp ON pp.ProductID = temp.ProductID
INNER JOIN Producttypes AS p ON p.Producttypeid = pp.Producttypeid
WHERE temp.Cusid =  '23'
 --GROUP BY pp.ProductID -->This should be removed as it causes to returne the first of each other field while keeping ProductID distinct in the returned table.

My sql returns the first row each time,

1 Answers1