Thread

1. Select problem

   Karl F. Larsen <k5di@zianet.com> — 2000-08-14T15:29:54Z

   	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  -
   
   
   

2. Re: Select problem

   Chris Ryan <chris@greatbridge.com> — 2000-08-14T15:35:52Z

   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  -
   
   

3. Re: Select problem

   NRonayette <r97m10@socotec.fr> — 2000-08-14T15:55:44Z

   "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.
   
   
   
   
   

4. Re: Select problem

   John McKown <jmckown@prodigy.net> — 2000-08-14T16:35:29Z

   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