Two outer joined tables in a report without duplicates from both sides

 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.

- mgcWarrantsDetails (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.

This is how you can achieve it. 

/// <summary>
    /// Process report data.
    /// </summary>
    public void processReport()
    {
        Query                   query;
        QueryRun                qr;
        mgcWarrantsDetails      details;
        LedgerJournalTrans      trans;
        ProjId                  prevProjId;
        // merge trans and detail even if details are empty
        void insertWithTrans()
        {
            mgcProjWarrantDetailTmp.clear();
            mgcProjWarrantDetailTmp.RefRecId            = trans.RecId;
            mgcProjWarrantDetailTmp.JournalNum          = trans.JournalNum;
            mgcProjWarrantDetailTmp.TransDate           = trans.TransDate;
            mgcProjWarrantDetailTmp.Voucher             = trans.Voucher;
            mgcProjWarrantDetailTmp.ProjId              = trans.mgcProjId;
            mgcProjWarrantDetailTmp.WarrantDetailId     = details.WarrantDetailId;
            mgcProjWarrantDetailTmp.WarrantsWithPrice   = details.WarrantsWithPrice;

            mgcProjWarrantDetailTmp.insert();
        }
        // merge empty trans and detail; it will be 'a hanging' detail
        void insertWarrants()
        {
            mgcProjWarrantDetailTmp.clear();
            mgcProjWarrantDetailTmp.ProjId              = details.ProjId;
            mgcProjWarrantDetailTmp.WarrantDetailId     = details.WarrantDetailId;
            mgcProjWarrantDetailTmp.WarrantsWithPrice   = details.WarrantsWithPrice;

            mgcProjWarrantDetailTmp.insert();
        }
        // process warrants for the previous projId
        // 'hanging' details on the right side of the report
        void processRestOfWarrants()
        {
            // if there are still some warrants we found for the previous projId
            if (details)
            {
                // as we do not have enough rows from trans with the same previous projId,
                // let's add them with empty part from trans side
                next details;
                while(details)
                {
                    insertWarrants();
                    next details;
                }
            }
        }

        // Get the query from the runtime using a dynamic query.
        query = this.parmQuery();
        qr = new QueryRun(query);
        // we suppose that trans sorted by projId
        // each projId may have 0..N of warrants (details)
        while(qr.next())
        {          
            // get next transaction
            trans =  qr.get(tablenum(LedgerJournalTrans));  
            // projId changes 
            if(prevProjId != trans.mgcProjId)
            {
                // if there are still some warrants we found for the previous projId
                processRestOfWarrants();
                // get the first warrant for the new projId 
                select details
                    where details.ProjId == trans.mgcProjId;
                // keep the new as a previous for the next iteration
                prevProjId = trans.mgcProjId;
            }
            // if this is a trans with the same projId, then we can merge it with the next warrant
            else if (details)
            {
                next details;
            }
            // merge trans with warrants
            insertWithTrans();
        }
        // if there are still some warrants we found for the previous projId
        processRestOfWarrants();
    }

Raw outer joined data

Ascending ProjId

Descending ProjId