Self Join

[Foesi61]

Hello everyone

I have two tables: BomHead and BomLine.
My SQL looks like this.

SELECT

    BH.Part         AS 'Part'

    ,BL.Part        AS 'Pos1'

    ,BL2.Part       AS 'Pos2'

FROM BomHead AS BH

    JOIN BomLine AS BL

        ON BH.BomHeadMaster = BL.Master

 JOIN BomHead AS BH2

        ON BH2.Part = BL.Part

JOIN BomLine AS BL2

        ON BH2.BomHeadMaster = BL2.Master

How can I create this SQL in the data editor?

Thank you
Fösi

[Anil_Babu_Samineni]

@Foesi61 You can call directly SQL query by adding SQL in front and connection name.

Lib Connect to 'Your connection name';

SQL SELECT

    BH.Part         AS 'Part'

    ,BL.Part        AS 'Pos1'

    ,BL2.Part       AS 'Pos2'

FROM BomHead AS BH

    JOIN BomLine AS BL

        ON BH.BomHeadMaster = BL.Master

 JOIN BomHead AS BH2

        ON BH2.Part = BL.Part

JOIN BomLine AS BL2

        ON BH2.BomHeadMaster = BL2.Master

[Or]

You can just copy-paste this exact SQL if it already does what you want.

If you're trying to replicate this with Qlik tables, you'd need to load the tables individually - both the required fields and the key fields and use JOIN between loads. Once joined, you can drop the key fields using the Drop Field statement if desired.

[Foesi61]

This SQL is just control for me.
In reality these are two tables two qvd files.
So I need to get the same result across these two qvd's.

[Foesi61]

Hello @Or

This is exactly where I have problems with loading and joining individually.

[Or]

Typically, this would look something like:

Load FieldName, KeyField From Table1;

JOIN

Load FieldName as Field_Line, KeyField, SecondKeyField from Table2;

JOIN

Load SomeField as SecondKeyField, FieldName as Field_Second_LineFrom Table1;

Drop Field KeyField;

Drop Field SecondKeyField;

Note that if you just need one field from a table, it may be better to use a Mapping Load / ApplyMap() rather than a full join.

[Foesi61]

Hi @Or

I will try out your suggestion.

Thank you very much