Joining Two Databases with Different Query Structures

When working with multiple databases that require different query structures, it can be challenging to combine their data. In this case, we need to join two databases: one with a sum query and another without.

Understanding the Query Structure

Let’s break down the provided query:

1. First Database: test - This database has a self-join with itself, using an inner join on the load column.
2. Second Database: GetJoinedData - This database uses an outer join to combine data from test and ADC_get_PICK_SUM.

Analyzing the Data

From the provided output table, we can see that there are three columns missing: date, time, destination, and ship_to. These columns should be present in both databases.

Solving the Problem

To solve this problem, we need to create a temporary table or view that combines the data from both databases. Here’s one possible approach:

1. Create a Temporary View: Create a temporary view that selects all columns from both databases and joins them on the load column.
2. Use a Common Table Expression (CTE): Use a CTE to simplify the query and make it easier to read.

Example Solution

<!-- Create a temporary view -->
<cfquery name="GetJoinedData" dbtype="query">
    SELECT 
        t.datetime,
        t.time,
        t.destination,
        t.ship_to,
        sum(t.lqty) as lqty,
        sum(a.scan_quantity) as scan_quantity
    FROM test t
    LEFT JOIN ADC_get_PICK_SUM a ON t.load = a.load
    GROUP BY datetime, time, destination, ship_to
</cfquery>

<!-- Use a CTE to simplify the query -->
<cfoutput query="GetJoinedData">
    <table class="back" bgcolor="grey" cellpadding="1" cellspacing="1" border=0 align="center">
        <tr bgcolor="Silver">
            <th>Date</th>
            <th>Time</th>
            <th>Load</th>
            <th>Destination</th>
            <th>Ship To</th>
            <th>Status</th>
            <th>Scanned</th>
            <th>Closed</th>
        </tr>
        <cfoutput query="GetJoinedData">
            <tr bgcolor="white">
                <td>#right(datetime,10)#</td>
                <td>#left(datetime,4)#</td>
                <td>#load#</td>
                <td>#destination#</td>
                <td>#ship_to#</td>
                <td=#lqty#</td>
                <cfif scan_quantity eq 0>
                    <td bgcolor="red">Empty</td>
                </cf elif scan_quantity lt sum(a.scan_quantity)>
                    <td bgcolor="green">Partially scanned</td>
                </cf else>
                    <td>#scan_quantity#</td>
                </cf if>
                <cfif status eq 0>
                    <td>Yes</td>
                </cf else>
                    <td>No</td>
                </cf if>
            </tr>
        </cfoutput>
    </table>
</cfoutput>

Explanation

In this solution, we create a temporary view GetJoinedData that selects all columns from both databases and joins them on the load column. We use a CTE to simplify the query and make it easier to read.

We then use this CTE as the output of our main query, which generates the final table with the desired columns.

Note: This solution assumes that you have the necessary permissions to create temporary views in your database. Additionally, the scan_quantity column is assumed to exist in both databases and has a matching data type.

Last modified on 2023-08-24