Why is LEFT JOIN creating extra rows?

[#1]

Having a bad QV day today 😞

I have a master table with 100,000 rows and a lookup table with 100 rows linked by an ID field.

When I load both everything works fine with the lookup table having 100 unique ID's and QV performing as I would expect.

But if I load the lookup table then the master table and then try a left join to suck in a lookup field from the lookup table I end up with 10,000,000 rows in the master table!!! I might expect this if all the ID fields where blank or null, but they are definitely all populated with sensible data.

Lookup:

load

fieldA,

fieldB,

ID

from lookup.qvd (qvd);

Master:

load

field1,

field2,

ID

from master.qvd (qvd);

left join load

fieldA

resident Lookup;

Any help welcome 🙂 Thanks

[Anonymous · Accepted solution]

Hi isawyer,

You need to include the ID in the resident load that you perform the left join on, otherwise you don't have any matching fields for the join to perform like you expect. Instead you end up with a carthesian product, 100000*100 rows.

//JSN

[#3]

Hi JSN,

Yes, you are correct, my brain was assuming that QV knew about the common ID already and therefore didnt need to be told.

Thats one for the GOTCHA list

Thanks very much 🙂