Re: Drill selects column with the same name of a different table

[Divya Gehlot <di...@gmail.com>]

Hi,
At times if during the join if the columns names are same the drill suffix
zero for second column name .
like in your case it could be name ,name0,

> Can you please do a select * on join tables and check the column headers ?



Thanks,
Divya

On 21 August 2017 at 22:04, Hielke Hoeve <Hi...@topicus.nl> wrote:

> Hi everyone. I just started using Drill/Zeppelin and I’m facing a strange
> problem. I have a PostgreSQL database linked to a Drill instance. Whenever
> I am trying to join 2 tables which both have a column name and whenever I
> want to select this name Drill selects the wrong name column. What am I
> doing wrong?
>
> Given the following 2 tables:
>
> Department
> | id | name |
> |----|------|
> | 1  |  A   |
> | 2  |  B   |
>
> Employee
> | id | name | dept | salary |
> |----|------|------|--------|
> | 1  |  U   |  1   |  100   |
> | 2  |  V   |  1   |   75   |
> | 3  |  W   |  1   |  120   |
> | 4  |  X   |  2   |   95   |
> | 5  |  Y   |  2   |  140   |
> | 6  |  Z   |  2   |   55   |
>
> Running
>
> select employee.name, employee.salary
> from employee
> inner join department on employee.dept = department.id
> where department.name = 'A'
>
> returns
>
> | name | salary |
> |------|--------|
> |  A   |  100   |
> |  A   |  75    |
> |  A   |  120   |
>
> Running
>
> select dept.name, employee.salary
> from employee
> inner join department on employee.dept = department.id
> where department.name = 'A'
>
> returns
>
> | name | salary |
> |------|--------|
> | null |  100   |
> | null |  75    |
> | null |  120   |
>
>
> What does work, but seems very silly to me, is:
>
> select dept.name, employee.salary
> from employee
> inner join (select id, name as deptname from department) as department on
> employee.dept = department.id
> where department.deptname = 'A'
>
>
>