My query returns each time the same (date2)

Question

SELECT  o.Date1, o.Date2 
FROM customers AS c, products AS p, products_ids AS pi, orders AS o, temptable AS te 
WHERE p.productid = pi.productidD 
AND pi.prodno = te.prodno 
AND c.custid = o.custid
AND o.custid = te.custid 
AND te.custid =  '24' 
GROUP BY pi.productiD

The result of the query returns each time the same date(of one row) to all the rows,even if the dates are different inside mysql.

. . You need to learn proper join syntax. Once you write it with `join`, you won't have the problem of an unexpected cartesian product. — Gordon Linoff, Apr 24 '13 at 22:01
@marina here's a good place to start http://stackoverflow.com/questions/44917/explicit-vs-implicit-sql-joins — Matt Busche, Apr 24 '13 at 22:09
@GordonLinoff I think "proper join syntax" is a slight misnomer, although I am an advocate of ANSI 92 explicit joins, ANSI 89 implicit joins are still proper joins, and in some instances Oracle will optimise these better than explicit joins so they still have their place. Aaron Bertrand does give some pretty compelling reasons to switch to the newer syntax in one of his blogs though https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins — GarethD, Apr 24 '13 at 22:12

GarethD · Accepted Answer · 2013-04-24T22:42:34.440

As has been mentioned it is worth switching to the newer ANSI 92 JOIN syntax for clarity, this can avoid accidental cross joins, so your query would become:

SELECT  o.Date1, o.Date2 
FROM    customers AS c
        INNER JOIN orders AS o
            ON c.custid = o.custid
        INNER JOIN temptable AS te 
            o.custid = te.custid 
        INNER JOIN products AS p
            ON pi.prodno = te.prodno
        INNER JOIN products_ids AS pi
            ON p.productid = pi.productidD 
WHERE   te.custid =  '24' 
GROUP BY pi.productiD;

However, this is not the problem in your query, the problem is the fact that you are abusing the functionality within MySql whereby you can select items that are not part of the group by:

Here you are telling the query to select 1 row per distinct value for pi.ProductID, there could be multiple different values for o.Date1 and o.Date2 for each pi.ProductID, and you are giving the query no hints as to which of these multiple possibilities to choose from.

Using columns in the select that are not in the group by is permitted by ANSI standards but only when the columns in the select are functionally dependant on columns within the group by, this is very hard to adhere to so most DBMS simply do not allow columns in the select that are not in the group by.

To demonstrate consider this dataset (T)

OrderID ProductID   Date1       Date2
1       1           20130401    20130402
2       1           20130402    20130403
3       2           20130403    20130404
4       2           20130404    20130405
5       2           20130405    20130406

If (as in your case) you did:

SELECT  OrderID, ProductID, Date1, Date2
FROM    T
GROUP BY ProductID

You would probably get:

OrderID ProductID   Date1       Date2
1       1           20130401    20130402
3       2           20130403    20130404

Just taking the first row for each product ID, however since the query is non deterministic it would be equally correct for the query to return the last row for each productID:

OrderID ProductID   Date1       Date2
2       1           20130402    20130403
5       2           20130405    20130406

It is a common myth that adding order by will help this, e.g.

SELECT  OrderID, ProductID, Date1, Date2
FROM    T
GROUP BY ProductID
ORDER BY OrderID;

MySql determines the rows to be returned before applying the order by, so even adding ORDER BY OrderID does not guarantee the first order for each productID is returned.

The MySQL Docs state

The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate. Furthermore, the selection of values from each group cannot be influenced by adding an ORDER BY clause.

Finally to elaborate on what I said about functionally dependant columns, because in this dataset OrderID is a primary key, according to ANSI standards you could write:

SELECT  OrderID, ProductID, Date1, Date2
FROM    T
GROUP BY OrderID;

So, while I have not really answered your question I hopefully have shown where you are going wrong. You really need to establish what you are trying to achieve by the group by, and if you still haven't got a solution then i'd suggest a new, more constructive question, with sample data and an expected output with clear rules as to why the expexted output is expexted.

My query returns each time the same (date2)

1 Answers1