Correlated SQL Server Queries

Question

I currently have SQL code that looks like this.

SELECT
    WORKORDER_BASE_ID As 'Work Order',
    SCHED_START_DATE AS 'Scheduled Start',
    SETUP_HRS AS 'Approx Setup Hrs',
    RUN_HRS AS 'Approx Run Hrs',
    (
        SELECT 
            PART_ID as "Components" 
        FROM 
            REQUIREMENT 
        WHERE 
            REQUIREMENT.WORKORDER_BASE_ID = OPERATION.WORKORDER_BASE_ID
            AND REQUIREMENT.WORKORDER_SUB_ID = OPERATION.WORKORDER_SUB_ID
            AND PART_ID LIKE '%PSU%'
    )   AS PSU
FROM 
    OPERATION
WHERE 
    OPERATION.STATUS = 'R' 
    AND RESOURCE_ID LIKE '%{Root Container.equipmentName}%'

I get errors because a subquery generates more than one field. I need a way to specify a condition for a subquery in order to display only data related to a specific order of work on this line, in particular the number of a work order. I guess this is some kind of loop function.

Any suggestions?

BTW, the bottom line is correct. The platform used interprets the values ​​in braces as local variables.

Answer 1

- select . TOP 1 , . ....

SELECT OPERATION.WORKORDER_BASE_ID   AS [Work Order]
      ,OPERATION.SCHED_START_DATE    AS [Scheduled Start]
      ,OPERATION.SETUP_HRS           AS [Approx Setup Hrs]
      ,OPERATION.RUN_HRS             AS [Approx Run Hrs]
      ,REQUIREMENT.PART_ID           AS [Components] 
FROM  OPERATION
INNER JOIN REQUIREMENT ON REQUIREMENT.WORKORDER_BASE_ID = OPERATION.WORKORDER_BASE_ID
                      AND REQUIREMENT.WORKORDER_SUB_ID = OPERATION.WORKORDER_SUB_ID
WHERE OPERATION.[STATUS] ='R' 
  AND OPERATION.RESOURCE_ID LIKE '%{Root Container.equipmentName}%'
  AND REQUIREMENT.PART_ID LIKE '%PSU%'