Problem with LEFT JOIN using Pentaho Kettle

Question

Navigation

#1 by (2 votes)

5

There is behavior regarding JOIN that I can not understand.

I have two tables in these formats

Sick Table

 ID_Doente Doente 
 1          Pedro
 2          Paulo
 3          Rui

Query table

 ID_Consulta  ID_Doente Tipo
 1            2        Não Medica
 2            2        Médica

And I'm doing a left join like this:

SELECT *
FROM Consulta
LEFT JOIN Doente
ON Consulta.ID_Doente =Doente.ID_Doente;

I was with the idea that the result would look something like this

ID_Consulta  ID_Doente    Tipo         Doente
 1             2        Não Medica    Paulo
 2             2        Médica        Paulo

But I'm having this

ID_Consulta  ID_Doente    Tipo         Doente
 1             2        Não Medica    Paulo
 2             2        Médica        NULL

Does anyone know the reason?

EDIT: This is an example of what is happening, I am accomplishing this using the merge left join functionality of Pentaho Kettle

EDIT2: The question was answered here link

sql join pentaho-kettle

asked by anonymous 17.03.2014 / 16:44

1 answer

Image limit per line using FlexBox https redirect problem

score 2 · Answer 1

I tested your SQL in SQLServer and it worked normally:

The problem should be in the Pentaho Kettle engine.

The following code is the same, but try to see if it works, as it should be a structural problem in Pentaho.

SELECT *
FROM Doente 
RIGHT JOIN Consulta
ON Consulta.ID_Doente =Doente.ID_Doente;