Write a join subquery within a select query-CodePudding

I have this query that works in a separate worksheet.

SELECT
location_key AS carrier_company_name,
FROM INGEST.LOCATION_
JOIN INGEST.LOAD_ 
ON INGEST.LOAD_.dwh_masterclient_id = INGEST.LOCATION_.dwh_masterclient_id

Now, I want to use it within another query. So basically, I want to select the dwh_masterclient_id from the LOAD table and then I want to select the location_key AS carrier_company_name from the LOCATION table after joining on the basis of dwh_masterclient_ids that exist in both tables. I am trying this

USE DATABASE PROD_DWH;
SELECT
cast(dwh_masterclient_id as smallint) AS dwh_masterclient_id,
(SELECT
location_key
FROM INGEST.LOCATION_
JOIN INGEST.LOAD_ 
ON INGEST.LOAD_.dwh_masterclient_id = INGEST.LOCATION_.dwh_masterclient_id),
1 as exception_codes
FROM INGEST.LOAD_

but it gives me an error

Single-row subquery returns more than one row.

CodePudding user response：

To allow several location rows, skip the subquery and do a LEFT JOIN instead. Something like:

SELECT cast(dwh_masterclient_id as smallint) AS dwh_masterclient_id,
       location_key,
       1 as exception_codes
FROM INGEST.LOAD_
LEFT JOIN INGEST.LOCATION_
  ON INGEST.LOAD_.dwh_masterclient_id = INGEST.LOCATION_.dwh_masterclient_id