Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
Foesi61
Contributor

Self Join

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

 

Labels (1)
6 Replies
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

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
Or
MVP

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
Contributor
Author

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
Contributor
Author

Hello @Or

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

Or
MVP

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
Contributor
Author

Hi @Or

I will try out your suggestion.

Thank you very much