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

[Martin Gainty <mgainty@xxxxxxxxxxx>]

daniel

what happens when you implement the INNER JOIN statement and remove group by
?

Martin
______________________________________________
Disclaimer and Confidentiality/Verzicht und Vertraulichkeitanmerkung / Note de déni et de confidentialité

This message is confidential. If you should not be the intended receiver, then we ask politely to report. Each unauthorized forwarding or manufacturing of a copy is inadmissible. This message serves only for the exchange of information and has no legal binding effect. Due to the easy manipulation of emails we cannot take responsibility over the the contents.
Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen.
Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni.

> From: daniele.pignedoli@xxxxxxxxx
> Subject: Re: PGSQL-to-MYSQL Migration: Error in a 'simple' inner join query
> Date: Tue, 5 May 2009 00:10:56 -0700
> To: pgsql-general@xxxxxxxxxxxxxx
>
> Hi guyst.. thanks for the replies, really, them make me suppose that
> all what i've learned of sql from mysql can be wrong..
>
> 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:
> ---
> 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
> ---
> 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?
> but aswell i cant understand why i must group for the costumer name,
> when i dont want and dont need it to be *unique*
>
> So, lets suppose that i want to retrieve the total price of every item
> bought by every customer, i'll start with this query:
> ---
> SELECT
> customer.name,
> SUM(order_item.price)
> FROM
> customer
> INNER JOIN orders ON (orders.id_customer = customer.id)
> INNER JOIN order_item ON (order_item.id_order = orders.id)
> GROUP BY customer.id
> ---
> but still, SQL throw an error, and i must add the costumer.name in the
> group clause.
> And still, that will be *ignored* becose the *main* GROUP BY is
> customer.id.. and the query works.
>
> So, suppose i have thosa data:
> customer
> id | name
> 1 | Abigail
> 2 | Steve
>
> orders:
> id | id_customer | code
> 1 | 1 | 0001
> 2 | 2 | 0002
> 3 | 1 | 0003
>
> order_item:
> id | id_order | price |
> 1 | 1 | 21.23 |
> 2 | 1 | 21.32 |
> 3 | 2 | 21.23 |
> 4 | 2 | 21.32 |
> 5 | 3 | 21.23 |
> 6 | 3 | 21.32 |
> So, Abigail have 2 orders, steve 1, every order have 2 items.
>
> If i would to retrieve the number of orders, the number of total items
> bought (total sum of every order) for every customer, the query shuold
> be like that:
> ---
> SELECT
> customer.name,
> COUNT(orders.id),
> (
> SELECT
> COUNT(order_item.id)
> FROM
> orders
> INNER JOIN order_item ON (order_item.id_order = orders.id)
> WHERE orders.id_customer = customer.id
> GROUP BY orders.id_customer
> ) AS total_items
> FROM
> customer
> INNER JOIN orders ON (orders.id_customer = customer.id)
> GROUP BY customer.id, customer.name
> ---
>
> Right?
>
> --
> Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

---

Windows Live™: Keep your life in sync. Check it out.