--- In firebird-support@yahoogroups.com, "Adam" <s3057043@...> wrote:

>
> --- In firebird-support@yahoogroups.com, "jack_it_m" <jack_it@> wrote:
> >
> > Hello all,
> >
> > when I try to execute the following query against the employee.fdb
> > database
> >
> > SELECT DEPARTMENT1.DEPARTMENT, DEPARTMENT1.LOCATION
> > FROM DEPARTMENT DEPARTMENT
> > INNER JOIN DEPARTMENT DEPARTMENT1 ON DEPARTMENT.DEPT_NO =
> > DEPARTMENT1.HEAD_DEPT
> > WHERE DEPARTMENT.MNGR_NO = 105
> >
> > I get no result, while if I run the following query
> >
> > SELECT DEPARTMENT1.DEPARTMENT, DEPARTMENT1.LOCATION
> > FROM DEPARTMENT A
> > INNER JOIN DEPARTMENT DEPARTMENT1 ON A.DEPT_NO =
> DEPARTMENT1.HEAD_DEPT
> > WHERE A.MNGR_NO = 105
> >
> > I get the following expected result
> >
> > DEPARTMENT LOCATION
> > Sales and Marketing San Francisco
> > Engineering Monterey
> > Finance Monterey
> >
> > Where is the mistake?
>
> Hmmm, A table, a field and an alias all with the same name. That is
> sadistic.
>
> Firebird is probably getting confused between your Department table
> and your Department alias. I know I certainly would be getting lost if
> I was maintaining such a query.
>
> SELECT D2.DEPARTMENT, D2.LOCATION
> FROM DEPARTMENT D1
> INNER JOIN DEPARTMENT D2 ON D1.DEPT_NO = D2.HEAD_DEPT
> WHERE D1.MNGR_NO = 105
>
> is much clearer and should work.
>
> Adam
>

More simply it could be a firebird bug!

Jack