I cannot find a particular term for this type of join; so I called it 'hanging left outer join'.
Let's state the problem.
We have two tables:
- LedgerJournalTrans (trans) with a field mgcProjId, which is a reference to ProjTable.
- myDetails (details), which may have 0..N records for certain records from ProjTable.
Examples of data in both may look like the following:
If we apply standard outer join, the output will be as follows
But we need to print all details records by 'hanging' them against the same projId, like this
So, no duplicates from both tables must be present in the merged table myDetailsTmp.
This is how you can achieve it.