Brad Hilton <bhilton@vpop.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.
> Postgres evidently doesn't work this way. Is there any way to select
> all fields from a table without grouping on each of those fields if the
> group by field is a unique key?
Please define what answer you want, and then we can talk about how to
get it.
> The second problem is with LEFT JOIN. Here's a sample query that works
> on Mysql but not on postgresql:
> select count(*) from a, b
> LEFT JOIN c on
> c.foo = a.foo and
> c.foo = b.foo
> where
> c.foo is not null and
> a.aid = b.bid
> This raises an error:
> ERROR: JOIN/ON clause refers to "a", which is not part of JOIN
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)
from which the error complaint follows.
regards, tom lane