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