Postgres Pro
Downloads
Home   >   mailing lists

Thread: Select problem

Select problem

From
"Karl F. Larsen"
Date:
    I have a simple 3 table database with armycall as the common key
and it all works fine when I doa select like so:

    select user, doer
    from data, biglist
    where user.armycall = biglist.armycall;

but if I want to get the column armycall and use:

    select user, armycall
    from data, biglist
    where user.armycall = biglist.armycall

I get an error that armycall is ambiguous.

    Does anyone know how to correct this?

Yours Truly,

       - Karl F. Larsen, k5di@arrl.net  (505) 524-3303  -

RE: Select problem

From
patrick.wolf@Aerojet.com (WOLF, PATRICK)
Date:
try inserting the table name in the select, like this:

        select user, biglist.armycall
        from data, biglist
    where user.armycall = biglist.armycall

Pat
> -----Original Message-----
> From:    Karl F. Larsen [SMTP:k5di@zianet.com]
> Sent:    Monday, August 14, 2000 9:30 AM
> To:    pgsql-novice@postgresql.org
> Subject:    [NOVICE] Select problem
>
>
>     I have a simple 3 table database with armycall as the common key
> and it all works fine when I doa select like so:
>
>     select user, doer
>     from data, biglist
>     where user.armycall = biglist.armycall;
>
> but if I want to get the column armycall and use:
>
>     select user, armycall
>     from data, biglist
>     where user.armycall = biglist.armycall
>
> I get an error that armycall is ambiguous.
>
>     Does anyone know how to correct this?
>
> Yours Truly,
>
>        - Karl F. Larsen, k5di@arrl.net  (505) 524-3303  -

Re: Select problem

From
Chris Ryan
Date:
since armycall is a field in both tables postgres doesn't know which one
you are talking about. So in instances like these you have to fully
qualify the field name. i.e. data.armycall or biglist.armycall. Your
choice as in this instance the data will be the same. So the final query
should look like

    select user, data.armycall
        from data, biglist
        where user.armycall = biglist armycall

As a general rule when i join tables I ussually qualify all the fields
in the query to make it clear as that what fields i trully want.

hope this helps

Chris Ryan
chris@greatbridge.com



"Karl F. Larsen" wrote:
>
>         I have a simple 3 table database with armycall as the common key
> and it all works fine when I doa select like so:
>
>         select user, doer
>         from data, biglist
>         where user.armycall = biglist.armycall;
>
> but if I want to get the column armycall and use:
>
>         select user, armycall
>         from data, biglist
>         where user.armycall = biglist.armycall
>
> I get an error that armycall is ambiguous.
>
>         Does anyone know how to correct this?
>
> Yours Truly,
>
>          - Karl F. Larsen, k5di@arrl.net  (505) 524-3303  -

Re: Select problem

From
NRonayette
Date:
"Karl F. Larsen" a écrit :

>         I have a simple 3 table database with armycall as the common key
> and it all works fine when I doa select like so:
>
>         select user, doer
>         from data, biglist
>         where user.armycall = biglist.armycall;
>
> but if I want to get the column armycall and use:
>
>         select user, armycall
>         from data, biglist
>         where user.armycall = biglist.armycall
>
> I get an error that armycall is ambiguous.
>
>         Does anyone know how to correct this?

You must specify witch column you want in your select : data.armycall or
user.armycall (even if they are equal)

this give you something like this :
"
 select user, data.armycall
        from data, biglist
        where data.armycall = biglist.armycall
"
or
"
 select user, biglist.armycall
        from data, biglist
        where data.armycall = biglist.armycall
"

carefull, you made a mistake : "where user.armycall = biglist.armycall"
user is not a table but a column isn't it  ?


--
Workers of the world, arise!  You have nothing to lose but your chairs.

Re: Select problem

From
John McKown
Date:
On Mon, 14 Aug 2000, Karl F. Larsen wrote:

>
>     I have a simple 3 table database with armycall as the common key
> and it all works fine when I doa select like so:
>
>     select user, doer
>     from data, biglist
>     where user.armycall = biglist.armycall;
>
> but if I want to get the column armycall and use:
>
>     select user, armycall
>     from data, biglist
>     where user.armycall = biglist.armycall
>
> I get an error that armycall is ambiguous.
>
>     Does anyone know how to correct this?
>
> Yours Truly,
>
>        - Karl F. Larsen, k5di@arrl.net  (505) 524-3303  -
>

You need to tell the SELECT which armycall you want since it is in both
tables. Yes, the values are identical due to the WHERE clause, but the
parser really doesn't know this. So try:

select user, data.armycall
from data, biglist
where data.armycall = biglist.armycall

Note - I think you have a transcription error in your example because you
said user.armycall, not data.armycall in the WHERE clause.

Anyway, I hope this helps,
John

Re: Select problem

From
"Karl F. Larsen"
Date:
    You are exactly right Joe. Thanks and I found another way which
sometimes works. It goes

    select data.* , user
    from......

when you want all from table "data" and user from another.


On Mon, 14 Aug 2000, Joe LaLumiere wrote:

> >but if I want to get the column armycall and use:
> >
> >    select user, armycall
> >    from data, biglist
> >    where user.armycall = biglist.armycall
> >
> >I get an error that armycall is ambiguous.
> >
> >    Does anyone know how to correct this?
>
> Since you have two tables which have the same field name you need to specify which table to grab the field from.  The
correctedselect statement would be: 
>
>     select user, user.armycall
>     from data, biglist
>     where user.armycall = biglist.armycall
>
> OR
>
>     select user, biglist.armycall
>     from data, biglist
>     where user.armycall = biglist.armycall
>
> Either of these two will work.  The above applies to any two fields which have the same name in two or more tables.
>
> Joe LaLumiere
>
> >
> >Yours Truly,
> >
> >       - Karl F. Larsen, k5di@arrl.net  (505) 524-3303  -
> >
> >
> >
> ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
>  \   Joseph LaJeunesse-LaLumiere  /  Phone: (781) 276-6816     \  TIAC - The Internet Access Company  /
>   \  Systems Administrator I     /   Pager: (617) 473-2573      \      - A Division of Inter.net     /
>    \ Inter.net - Bedford MA     /    Email: joelight@tiac.net    \                                  /
>     ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
>
>

Yours Truly,

       - Karl F. Larsen, k5di@arrl.net  (505) 524-3303  -