LEFT JOIN wont work for qvds

[matthewp]

I have two QVD's that i want to left join on two fields

The code is as follows and i assume this is INNER JOINING both of them.

TABC:

LOAD BNumber AS [TAB B Number],

          [I Number] AS [TAB I Number]

FROM

(qvd);

TABI:

LOAD [TABI.B Reference] AS [TAB B Number],

          [TABI.TABC I Reference] AS [TAB I Number]

FROM

(qvd);

I have tried the following to LEFT JOIN them but it doesn't work and also creates a synthetic key.

QUALIFY *;

UNQUALIFY BNumber, [I Number];

TABC:

LOAD BNumber AS [TAB B Number],

     [I Number] AS [TAB I Number]

FROM

(qvd);

QUALIFY *;

UNQUALIFY [TABI.B Reference], [TABI.TABC I Reference];

LEFT JOIN (TABI)

TABI:

LOAD [TABI.B Reference] AS [TAB B Number],

     [TABI.TABC I Reference] AS [TAB I Number]

FROM

(qvd);

[Clever_Anjos]

What about:

TABC:
LOAD
  BNumber AS [TAB B Number],
    [I Number] AS [TAB I Number]
FROM (qvd);

LEFT JOIN(TABC)
LOAD
  [TABI.B Reference] AS [TAB B Number],
    [TABI.TABC I Reference] AS [TAB I Number]
FROM (qvd);

[matthewp]

This doesnt give me a correct left join, data wise it gives me results like an inner join

[Clever_Anjos]

Hard to guess without knowing more about your data.

That syntax is the correct one

[matthewp]

basically i want every thing from TABC and then LEFT (or LEFT OUTER) JOIN TABI on them two fields

[tamilarasu]

You are trying to left join two same fields. You can have one key field and a field you want to left join. In your case bothare key fields TAB B Number and  TAB I Number as per your script. you should rename TAB I Number to something else.

[MK_QSL]

Are you sure you are looking for ....

LEFT JOIN (TABI)

and not

LEFT JOIN (TABC)

[matthewp]

No thats gives me an error table not found

[matthewp]

so your saying you can only join tables on 1 field

[Anonymous]

this might be helpful for you:

Join by two fields

Left join with two key fields