Left Join with ON clause

[#1]

Hi,

I have two tables with that structure:

Table1:

id | Numb | date |

Table2:

Numb | dateInit | dateEnd | Loc

I want to do a left join between this two tables ( Numb )  in order to add the Loc field to the Table1 when  date >= dateInit AND date <= dateEnd

in MYSQL :

SELECT tb1.id,tb1.Numb,tb1.date,tb2.Loc FROM Table1 as tb1
      LEFT JOIN Table2 as tb2
             ON tb1.Numb = tb2.Numb AND tb1.date >= tb2.dateInit AND tb1.date <= tb2.dateEnd

Thanks in advance

Albert

[vikasmahajan]

What is a issue It seems this sql is ok

[alexandros17]

try this

SELECT tb1.id,tb1.Numb,tb1.date,tb2.Loc FROM Table1 as tb1
      LEFT JOIN Table2 as tb2
             ON tb1.Numb = tb2.Numb

WHERE

tb1.date >= tb2.dateInit AND tb1.date <= tb2.dateEnd

[lironbaram]

hi you should use interval match

i'll try to upload a demo later

[prasad_dumbre]

Hi,

Refer This link for eg:

http://community.qlik.com/blogs/qlikviewdesignblog/2013/04/04/intervalmatch

[#6]

Yes, i know the mYSQL sentenc is correct, but i want to do it on Qlikview Script.    

[alexandros17]

TMP_Table:

LOAD id,Numb,date FROM Table1

left join

LOAD Numb,| dateInit, dateEnd, Loc FROM Table2

MyTable:

noconcatenate

LOAD

     *

resident TMP_Table

where

date >= dateInit AND date <= dateEnd

Drop table TMP_Table;