Skip site navigation (1) Skip section navigation (2)

Re: switching from mysql

From: Brad Hilton <bhilton(at)vpop(dot)net>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: switching from mysql
Date: 2001-11-16 16:55:27
Message-ID: 1005929727.29326.0.camel@aragorn (view raw or flat)
Thread:
Lists: pgsql-novice
On Thu, 2001-11-15 at 22:16, Tom Lane wrote:
> Brad Hilton <bhilton(at)vpop(dot)net> writes:
> > I issue
> > select * from items GROUP BY id
> > I get:
> > "Attribute items.name must be GROUPed or used in an aggregate function"
> > It appears in MySQL if you group on a unique key, then you aren't
> > required to group on the rest of the fields you select from the table.
> 
> If you know it's a unique key, why are you bothering to GROUP?
> If it's not a unique key, then there's no unique value for the other
> columns.  Either way, I can see no sense to this query.  The SQL92
> spec doesn't see any sense in it either.

The example I gave was a simple one to demonstrate my problem.  Let me
give a better example.  Let's say I have 3 tables: items, stores, and
store_items.  'store_items' maps items to stores.  If I want to select
all items that are in at least one store, I currently do:

select items.* from items, store_items where items.id =
store_items.item_id group by items.id

Is there a better way to do this query?

> I believe MySQL thinks that this query means
> 
> 	... FROM (a CROSS JOIN b) LEFT JOIN c ON condition
> 
> which unfortunately for them is not what the SQL spec says the
> syntax means.  The spec's interpretation is
> 
> 	... FROM a CROSS JOIN (b LEFT JOIN c ON condition)
> 

Thank you for your help!

-Brad


In response to

Responses

pgsql-novice by date

Next:From: Helge BahmannDate: 2001-11-16 17:26:10
Subject: Re: switching from mysql
Previous:From: Jason EarlDate: 2001-11-16 16:44:13
Subject: Re: Perl and pgsql...

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group