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