Problem with LEFT JOIN using Pentaho Kettle

Question

There is a behavior with respect to JOIN that I cannot understand.

I have two tables in these formats

Table Doente

 ID_Doente Doente 
 1          Pedro
 2          Paulo
 3          Rui

Table query

 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 under the impression that the result would be something like this

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

But yet 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’s going on, I’m doing this using the Pentaho Kettle’s merge left Join feature

EDIT2: Question has been answered here https://stackoverflow.com/questions/22459143/pentaho-kettle-weird-left-join-results

Answer 1

I tested your SQL on Sqlserver and it worked normally:

The problem must be in the engine of the Pentaho Kettle.

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

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

Answer 2

Hi, how are you? I believe you may be using a Join without adding the Sort component or unexpected results may come out.

See what quotes the Kettle documentation.

When using a JOIN in Pentaho add a Step Sort with the Ids link so that the Pentaho can classify the results before joining, so it will be able to classify without major problems

I hope I’ve helped.