
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hello @Or
This is exactly where I have problems with loading and joining individually.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
