Обсуждение: Ambiguous columns

If I have two tables(taba, tabb) with the same column (column1) name and
try to do:

SELECT taba.column1 AS column1,tabb.column2 AS column2
    FROM taba AS a
         JOIN tabb AS b
         ON  taba.indexCol = tabb.indexCol
GROUP BY column1

It tells me that column1 is ambiguous. Is that to be expected? I can
semi see why - but I thought you could only only group on selected
fields, and the only 'selected' column1 is that from taba. Minor issue,
with easy work around.
--


Regards,

Steve Tucknott
ReTSol Ltd

DDI    01903 828769
MOBILE    07736715772





___________________________________________________________
Yahoo! Messenger - NEW crystal clear PC to PC calling worldwide with voicemail http://uk.messenger.yahoo.com

am  03.07.2005, um  8:17:01 +0100 mailte Steve Tucknott folgendes:
> If I have two tables(taba, tabb) with the same column (column1) name and
> try to do:
>
> SELECT taba.column1 AS column1,tabb.column2 AS column2
>     FROM taba AS a
>          JOIN tabb AS b
>          ON  taba.indexCol = tabb.indexCol
> GROUP BY column1
>
> It tells me that column1 is ambiguous. Is that to be expected? I can

untestet:

SELECT a.column1 AS column1,b.column2 AS column2
  FROM taba a
  JOIN tabb b
  ON  a.indexCol = b.indexCol
GROUP BY a.column1


Regards, Andreas
--
Andreas Kretschmer    (Kontakt: siehe Header)
Heynitz:  035242/47212,      D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
 ===    Schollglas Unternehmensgruppe    ===

Steve Tucknott <steve@retsol.co.uk> writes:
> If I have two tables(taba, tabb) with the same column (column1) name and
> try to do:

> SELECT taba.column1 AS column1,tabb.column2 AS column2
>     FROM taba AS a
>          JOIN tabb AS b
>          ON  taba.indexCol = tabb.indexCol
> GROUP BY column1

> It tells me that column1 is ambiguous. Is that to be expected?

Yes.

> I thought you could only only group on selected fields,

No, that's never been true.  You're confusing it with ORDER BY,
which has different rules.

            regards, tom lane

On Sun, Jul 03, 2005 at 08:17:01 +0100,
  Steve Tucknott <steve@retsol.co.uk> wrote:
> If I have two tables(taba, tabb) with the same column (column1) name and
> try to do:
>
> SELECT taba.column1 AS column1,tabb.column2 AS column2
>     FROM taba AS a
>          JOIN tabb AS b
>          ON  taba.indexCol = tabb.indexCol
> GROUP BY column1
>
> It tells me that column1 is ambiguous. Is that to be expected? I can
> semi see why - but I thought you could only only group on selected
> fields, and the only 'selected' column1 is that from taba. Minor issue,
> with easy work around.

You have things backwards. You can only select fields that are used in
the group by list or that are aggregates. You can have things used
for grouping that are not output in the select list.

Tom,
That could have been the case. The original query had both GROUP and
ORDER. I assumed (my mistake) that the error was being generated by the
GROUP clause. Why should ORDER by object - isn't that the same - ie it
can only order on the selected fields?


On Sun, 2005-07-03 at 17:07, Tom Lane wrote:
> Steve Tucknott <steve@retsol.co.uk> writes:
> > If I have two tables(taba, tabb) with the same column (column1) name and
> > try to do:
>
> > SELECT taba.column1 AS column1,tabb.column2 AS column2
> >     FROM taba AS a
> >          JOIN tabb AS b
> >          ON  taba.indexCol = tabb.indexCol
> > GROUP BY column1
>
> > It tells me that column1 is ambiguous. Is that to be expected?
>
> Yes.
>
> > I thought you could only only group on selected fields,
>
> No, that's never been true.  You're confusing it with ORDER BY,
> which has different rules.
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match
--


Regards,

Steve Tucknott
ReTSol Ltd

DDI    01903 828769
MOBILE    07736715772



___________________________________________________________
How much free photo storage do you get? Store your holiday
snaps for FREE with Yahoo! Photos http://uk.photos.yahoo.com

Steve Tucknott wrote:

>If I have two tables(taba, tabb) with the same column (column1) name and
>try to do:
>
>SELECT taba.column1 AS column1,tabb.column2 AS column2
>    FROM taba AS a
>         JOIN tabb AS b
>         ON  taba.indexCol = tabb.indexCol
>GROUP BY column1
>
>It tells me that column1 is ambiguous. Is that to be expected? I can
>semi see why - but I thought you could only only group on selected
>fields, and the only 'selected' column1 is that from taba. Minor issue,
>with easy work around.
>
>
Oracle has a fun bug in this case when it picks which table/column to do
the group by on randomly.  Or it uses some kind of crazy psychic power
to determine which table you DON'T mean, and use that.

Jeff