How to link an attribute to two different tables in MySql

Question

I am selecting 4 attributes from a shipping requisition table. I can extract an org code from the document number using SUBSTR( DOC_NBR, 1, 6 ) AS "Org Code", and I need to do this because the org code is not an attribute in the shipping requisition table.

Query Result

In the Customer table I have Org_Code and Site_ID along with many other attributes.

I would like to take the Org Code I extracted from the DOC_NBR and get the Site_ID from the Customer table. Any suggestions would be greatly appreciated.

SELECT
    DOC_NBR,
    SUBSTR( DOC_NBR, 1, 6 ) AS "Org Code",
    --C.SITE_ID,--I want to match the SITE ID to the Org Code. Site ID is in cls_CUSTOMER table.
    ASN,
    QTY_TOT_SHIP AS QTY,
    DATE_ALLOC AS SHIP_DATE 
FROM
    CLS_REQN_SHIP_ARCH
    --cls_CUSTOMER C 
WHERE
    ASN = 'R400-2A5-RED' 
    AND QTY_TOT_SHIP IS NOT NULL 
    --AND C.Site_ID = SUBSTR( DOC_NBR, 1, 6 ) 
ORDER BY
    SHIP_DATE DESC;

Answer 1

SELECT
  DOC_NBR,
  SUBSTR( DOC_NBR, 1, 6 ) AS "Org Code",
  SITE_ID,
  ASN,
  TY_TOT_SHIP AS QTY,
  DATE_ALLOC AS SHIP_DATE 
FROM
  CLS_REQN_SHIP_ARCH,
  cls_CUSTOMER C
WHERE
  ASN = 'R400-2A5' 
  AND QTY_TOT_SHIP IS NOT NULL
  AND C.ORG_CODE = SUBSTR( DOC_NBR, 1, 6 )
ORDER BY
  SHIP_DATE DESC;