Can I perform a Left Join on Primary Keys in a subquery?

Question

I have a question about a SQL query I am writing in Microsoft Access. I have two tables that include a version of a primary key that I am trying to join them on.

The first table (Table1) includes the PK # in this format: TORD46709NVSW. The last four characters can range from 3-4 letters and have many combinations.

The second table (Table 2) includes the PK # in this format: TORD00046709. There are always three zeroes after TORD (these 4 characters are always consistent).

I have written queries to get these two to the following format: TORD46709.

Table 1:

SELECT

 LEFT ([PKT_NUMBER_SFX], 9) AS

TORD_NUMBER

FROM Table_1;

Table 2:

SELECT

LEFT ([Transfer ID], 4) +

RIGHT ([Transfer ID], 5)

AS

TORD_NUMBER

FROM Table_2;

My issues is that I'm now trying to join these two tables, but am struggling. I am trying to write the above statements in a subquery in order to join but am now thinking I might need to do an update table query before joining. I would like to avoid the latter.

SELECT QlikView_Report.PKT_NUMBER_SFX, Transfer_Orders_OLD.[Transfer ID]
FROM QlikView_Report 
LEFT JOIN TORD_REPORT ON QlikView_Report.PKT_NUMBER_SFX = Transfer_Orders_OLD.[Transfer ID]
WHERE Transfer ID IN 
(SELECT
LEFT ([Transfer ID], 4) +
RIGHT ([Transfer ID], 5)
AS
TORD_NUMBER
FROM Transfer_Orders_OLD) AND  
(SELECT 
 LEFT ([PKT_NUMBER_SFX], 9) AS
TORD_NUMBER 
FROM QlikView_Report);

Let me know if you think it possible to join on these non-matching PK's.

Answer 1

I have changed table name from QlikView_Report to QlikView_Report_OG in from clause.

SELECT QlikView_Report_OG.[Pkt Ctl #], QlikView_Report_OG.[Customer PO], QlikView_Report_OG.[PKT_NUMBER_SFX], Transfer_Orders_OLD.[Origin], 
Transfer_Orders_OLD.[Destination], Transfer_Orders_OLD.[Status], QlikView_Report_OG.[Item Desc], 
QlikView_Report_OG.[Qty], QlikView_Report_OG.[Carton Number], QlikView_Report_OG.[Carton Status],
QlikView_Report_OG.[Load Number], QlikView_Report_OG.[Wave Number], QlikView_Report_OG.[Carton Tracking], 
QlikView_Report_OG.[Cancellation], QlikView_Report_OG.[Trailer #] FROM QlikView_Report_OG 
LEFT JOIN Transfer_Orders_OLD 
ON LEFT (QlikView_Report.PKT_NUMBER_SFX, 9) = (LEFT (Transfer_Orders_OLD.[Transfer ID], 4) + RIGHT (Transfer_Orders_OLD.[Transfer ID], 5));

Answer 2

You don't need any subquery but need to compare both columns within on clause of left join. Please try this.

    SELECT QlikView_Report.PKT_NUMBER_SFX, Transfer_Orders_OLD.[Transfer ID]
    FROM QlikView_Report 
    LEFT JOIN Transfer_Orders_OLD 
    ON LEFT (QlikView_Report.PKT_NUMBER_SFX, 9) = (LEFT (Transfer_Orders_OLD.[Transfer ID], 4) + RIGHT (Transfer_Orders_OLD.[Transfer ID], 5));

For which rows condition would match you will get data in both the columns if condition don't match then data will be available only in PKT_NUMBER_SFX column.

I have checked the query in MS Access database and it's working as desired.

Answer 3

There is no need of a subquery. Use the calculation directly in the join condition. Try this,

SELECT 
q.PKT_NUMBER_SFX
, t.[Transfer ID]

FROM 
QlikView_Report q
LEFT JOIN 
Transfer_Orders_OLD t

ON LEFT (q.[PKT_NUMBER_SFX], 9) = (LEFT (t.[Transfer ID], 4) +RIGHT (t.[Transfer 
ID], 5))