--- In firebird-support@yahoogroups.com, Brad Pepers <brad@l...>
wrote:

> I've found an odd thing in Firebird. I have a table that like this:
>
> create table test (
> test_id integer not null,
> name varchar(30) not null,
> parent_id integer,
> primary key (test_id),
> foreign key (parent_id) references test (test_id)
> );
>
> I insert one record into the table:
>
> insert into test (test_id,name,parent_id) values

(1,'test',NULL)

>
> Now I want to select the test_id, name, and name of the parent so I

did

> this:
>
> select test.test_id,test.name,parent.name from test left join
> test parent on test.parent_id = parent.test_id;
>
> This doesn't work though! It does on Sybase and PostgreSQL and if

I

> select * instead of specifying the fields then I see it all but of
> course the parent aliased ones are null since the parent id is null

but

> I'm selecting the test.test_id and test.name so I should get the

right

> values but I don't!
>
> If I change the join to use an alias for both tables then it will

work

> so something like this:
>
> select t.test_id,t.name,p.name from test t left join test p
> on t.parent_id = p.test_id
>
> But this is not required for the other databases I've tried and I

don't

> see why it should be needed! I'm using Firebird CS 1.5.2 4731 on

SuSE

> Linux 9.2.
>
> --
> Brad Pepers
> brad@l...

Brad,

It doesn't work because it is ambiguous (in FB 1.5). FB 1.5 allows
the following statement

select id
from tablea a

I know that FB 2 works differently. In FB 2, if you define a table
alias, then you can not refer to it other than that alias, so the
above query would need to become

select a.id
from tablea a

or

select id
from tablea

Your case looks like an extension to this. It is doing a join to
itself, and would therefore have two fields named test_id. In your
case, it chooses the "wrong" one.

The work around is simple, use aliases for both tables as you have
done in your second example.

Adam