Re: PGSQL-to-MYSQL Migration: Error in a 'simple' inner join query

On Tue, 2009-05-05 at 00:10 -0700, DaNieL..! wrote:
> But still i have some trouble to understand the functionality of the
> orders example.
> My first goal is to retrieve every order, the customer name, and the
> total of the idems per order.. so (from my point of view) i *dont*
> need and *dont* ant to group by the costumer name, becose every
> costumer can have many orders...
> and, as i understand the query:

If I understand you correctly: every customer can have many orders, and
every order can have many items, and you want the sum of all items per
order per customer.

So, you need to group by the combination of order and customer. Grouping
by the combination of those two is _not_ the same as grouping by order,
and then grouping by customer.

> ---
> SELECT
> orders.code,
> customer.name,
> SUM(order_item.price)
> FROM
> orders
> INNER JOIN customer ON (customer.id = orders.id_customer)
> INNER JOIN order_item ON (order_item.id_order = orders.id)
> GROUP BY orders.id, customer.name
> ---

In this query, you should "GROUP BY orders.code, customer.name".

> it *try* to group by the customer name, but *fail* becose the GROUP
> priority is at the order's code.. how much am i far from it?

What is "GROUP priority"? I don't think that has any meaning.

> but aswell i cant understand why i must group for the costumer name,
> when i dont want and dont need it to be *unique*

customer.name will _not_ necessarily be unique, if you "GROUP BY
orders.code, customer.name". The _combination_ of orders.code and
customer.name" will be unique.

Regards,
Jeff Davis