LEFT JOIN and RESIDENT

[#1]

Hello everybody,

I have 2 tables I want to join to filter information but i'm not arrived.

Tab1:

Date_Calendrier,

Jour,

Mois,

Annee,

Ferie;

Tab2:

NB_Heure1,

NB_Heure2,

Date_Temp,

Individu

And I want

Tab3:

Load

     Individu,

     Date_Temp,

     if(notnull(Tab1.Ferie),0,NB_Heure1+NB_Heure2) as NB_Heure3

Resident Tab2

where Date_Temp=Date_Calendrier

How I can my Tab 3???Have you any idea please???

Thank you for tyour help!

Lna

[its_anandrjs]

Hi,

I suggest you need to use isnull( ) function

and need to change in code some thing like below

Tab1:

Date_Calendrier,

Jour,

Mois,

Annee,

Ferie;

Tab2:

NB_Heure1,

NB_Heure2,

Date_Temp,

Individu

Tab3:

Load

     Individu,

     Date_Temp,

     if(isnull(Tab1.Ferie)>0,NB_Heure1+NB_Heure2) as NB_Heure3

Resident Tab2

where Date_Temp=Date_Calendrier

Let me know

HTH

Rgds

Anand

[#3]

Hi Anand,

Thank you for your help but The problem is the Where condition.

the error message it's Field Date_Calendrier unkow...

Have you any idea?

[SunilChauhan]

try this

where Date_Temp=Tab1..Date_Calendrier

or

else share the sample

[its_anandrjs]

Hi,

Check the date format of the two date field

Date_Temp = Date_Calendrier is same or not and i think naming convention please check once there is some small mistake i think.

Naming convention like Qualify or not

like Tab1, Tab2

When ever you use Qualify please check if it is some think like

Tablename1.Datefield1 = Tablename2.Datefield2

HTH

Rgds

Anand

[its_anandrjs]

Hi,

If it is Qualify try this

Tab1.Date_Temp = Tab2.Date_Calendrier

Rgds

Anand

[#7 · Accepted solution]

You have to create an intermediate table

Temp:

Noconcatenate

Tab2:

Load

NB_Heure1,

NB_Heure2,

Date_Temp,

Individu

Resident Tab2

Left join

Load

Date_Calendrier as Date_Temp,

Jour,

Mois,

Annee,

Ferie;

Resident Tab1;

Tab3:

Noconcatenate

Load

     Individu,

     Date_Temp,

     if(isnull(Ferie)>0,NB_Heure1+NB_Heure2) as NB_Heure3

Resident Temp;

Drop table Temp;

[#8]

Hi Everybody,

thank you for your help!

the exemple of Pauljin is very good...

bye