Left JOIN between tables with no keyid?

[#1]

Dear All,

I have two tables with no KEYID relation between them

TABLE1:

Q.

1

2

3

4

5

TABLE2:

FROMQ,

TOQ,

Desc,

0,10, Less than 10

10,20 Between 10 - 20

I'm tryin to do

LEFT JOIN (TABLE1) LOAD * RESIDENT TABLE2 Where Q>=FROMQ and Q<TOQ;

But I get system error: Q doensn't exist

[#2 · Accepted solution]

You can JOIN the tables first and then reload the data while doing the WHERE clause.

Like this ...

Table1:
LOAD * INLINE [
Q
1
2
3
4
5
];

Table2:
LOAD * INLINE [
QFrom, QTo, Desc
0, 10, Less than 10
10, 20, Between 10 and 20
];

JOIN (Table1)
LOAD *
RESIDENT Table2;

DROP TABLE Table2;

STORE * FROM Table1 INTO Table1.qvd (qvd);

DROP TABLE Table1;

Table1:
LOAD *
FROM Table1.qvd (qvd)
WHERE Q >= QFrom AND Q < QTo;

[#3]

Probably an even better script is ...

Table1:
LOAD * INLINE [
Q
1
2
3
4
5
];

Table2:
LOAD * INLINE [
QFrom, QTo, Desc
0, 10, Less than 10
10, 20, Between 10 and 20
];

LEFT JOIN (Table1)
LOAD *
RESIDENT Table2;

Table3:
NOCONCATENATE
LOAD *
RESIDENT Table1
WHERE Q >= QFrom AND Q < QTo;

DROP TABLE Table1, Table2;

[#4]

Hi

Can you just explain this code please.

LEFT JOIN (TABLE1) LOAD * RESIDENT TABLE2 Where Q>=FROMQ and Q<TOQ;

For what context are you trying to filter the values while doing a left join. I tried the code without using the where and it was working fine. Not only that , the code for ledt join should be like this without using the filter:

LEFT JOIN (TABLE2) LOAD * RESIDENT TABLE1;

[michael_anthony]

There is also a function called INTERVALMATCH that can link values from one table into an interval from another and create a join.

On it's own creates a Synthetic key b/w Table1 and Table2. I added a little to create Table3 and join the Desc back into it so end up with one table.

Table1:
LOAD * INLINE [
Q
1
2
3
4
5
15
];

Table2:
LOAD * INLINE [
QFrom, QTo, Desc
0, 10, Less than 10
10, 20, Between 10 and 20
];

Table3:
INTERVALMATCH (Q)
NOCONCATENATE LOAD QFrom, QTo RESIDENT Table2;

LEFT JOIN (Table3) LOAD
QFrom,
QTo,
Desc
RESIDENT Table2;

DROP TABLE Table1;
DROP TABLE Table2;