At 04:35 PM 9/15/2001 +0200, Yves Glodt wrote:

>SELECT ADDRESS.CATEGORY, COUNT(ADDRESS.KEYINDEX), CATEGORIES.NAME
> FROM ADDRESS,CATEGORIES
> WHERE ADDRESS.CATEGORY=CATEGORIES.C_NUMBER
> GROUP BY CATEGORY;
>
>
>This is the resulting error:
>-------------------------------
>Statement failed, SQLCODE = -104
>Dynamic SQL Error
>-SQL error code = -104
>-invalid column reference
>
>
>I've tried *exactly* this query with the same db on mysql, and I got a
>result. Therefo I think that the syntax in itself is correct.

Actually, that's mysql playing a bit fast and loose with the SQL standard.
According to the standard, a query that includes a grouping expressing
(i.e. GROUP BY) can retrieve columns listed in the grouping expression
(i.e. CATEGORY) and aggregates of other columns. This makes a certain
amount of sense if you think about a query like this:

select state, city from states group by state;

There are lots of cities in most states. Which is the right one to
return?

Now you know that for each CATEGORY value from ADDRESSES there is exactly
one CATEGORIES record, so, to you, your query makes sense. The SQL
committee doesn't trust you to make that sort of decision, so it requires
you to express your query like this:

>SELECT A.CATEGORY, COUNT(A.KEYINDEX), C.NAME
> FROM ADDRESS A, CATEGORIES C
> WHERE A.CATEGORY = C.C_NUMBER
> GROUP BY A.CATEGORY, C.NAME;

^^^^^^^^
or this:

>SELECT A.CATEGORY, COUNT(A.KEYINDEX), MAX (C.NAME)
> FROM ADDRESS A, CATEGORIES C ^^^^
> WHERE A.CATEGORY = C.C_NUMBER
> GROUP BY A.CATEGORY;

(Actually the standard doesn't care about the paragraphing, the
spaces, or the aliasing, those are all my prejudices.)


Regards,

Ann
www.ibphoenix.com
We have answers.