LEFT JOIN and slow reload

[#1]

Hi,

I need to implement in QV script a LEFT JOIN between 2 different table,

Example :

SELECT A.field1, A.field2, B.field1 FROM A LEFT JOIN B ON A.key=B.key

So I want all the rows of A, even when no correspondence in B is found.
In my example table A contains 10 records while table B contains 500.000 records.

When I launch the script inside QV I obtain as result 10 records but QV loads all the 500.000 records, so it takes a lot of time.
How can I edit the script to let the reload faster ?

Thanks a lot.

Bye

[#2]

Hi

You can use EXISTS function,

A:
Load Field1, Field2 from A;

B:
Load Field1 from B
WHERE EXISTS(Field1,Field1)

Regards,
Amit

[#3]

check the attached example.

-Raghu

[#4]

Hi,

youmaybe mean to do

WHERE EXISTS(Field1,Field2) ?

I can't understand the meaning of this instruction.

Thanks

[johnw]

WHERE EXISTS(A,B) says to load field B where it has a value that is already in field A. You can use it with the same field name as well, WHERE EXISTS(A,A) or the shorthand version, WHERE EXISTS(A). That says to only load field A from the new source where the value already exists in the current data set. That's basically what you want here. However, your example will actually join on Field1 as well since it has the same name in both tables. If it's supposed to be a separate field, maybe something like this:

LOAD Key, Field1a, Field2
FROM your source
;
LEFT JOIN LOAD Key, Field1b
FROM your other source
WHERE EXISTS(Key)
;