Using Result Set to Re-Run Query Using Same Table

Transact-SQL
1

I'm using one table and two fields to extract data. What I'm try to achieve is to use the results from on field NLA and re-run the query from the same table kind of looping through. Can I use a sub query to achieve this or do I need to loop through the results? How would I write the query. Below is a sample what I'm trying to do. Your help would be much appreciated.

Regards,

SELECT "PN_NEXT_LOWER_ASSEMBLY"."NHA_PN" AS "NHA_PN",
"PN_NEXT_LOWER_ASSEMBLY"."NLA_PN" AS "NLA_PN"
FROM PN_NEXT_LOWER_ASSEMBLY
WHERE ( "PN_NEXT_LOWER_ASSEMBLY"."NHA_PN" = '71-20002-1'

RESULTS

NHA NLA
71-20002-1 2293B020000
71-20002-1 32175-3
71-20002-1 45731-1391
71-20002-1 CFM56-53B-P

Now I want to use each result from the NLA field and replace the above query with

WHERE ( "PN_NEXT_LOWER_ASSEMBLY"."NHA_PN" = '2293B020000') AND IF THE RESULT P/N HAS NO NLA SKIP TO NEXT RESULT P/N to achieve

NHA NLA
CFM56-5B3-P 10-617980-1
CFM56-5B3-P 337-075-105-0
CFM56-5B3-P 338-089-702-0

2

Do you mean something like this:

select a2.nha_pn
      ,a2.nla_pn
  from pn_next_lower_assembly as a1
       inner join pn_next_lower_assembly as a2
               on a2.nha_pn=a1.nla_pn
 where a1.nha_pn='71-20002-1'
3

["SOAPBOX"]
"FIRST","I'D" "LIKE" "TO" "SUGGEST" "THAT" "YOU" "STOP" "PUTTING" "QUOTES" "AROUND" "YOUR" "SCHEMA" "OBJECT" "NAMES." "IF" "NOTHING" "ELSE", "A" "SINGLE" "SET" "OPERATION" "CAN" "RENDER" "YOUR" "CODE" "UNCOMPILEABLE". "NEXT", "IF" "I" "MAY", "STOP" "USING" "ALL" "CAPS".
[/"SOAPBOX"]

To answer your question, it looks like you want to make use of a recursive CTE (Common Table Expression). Books Online has the details. They can appear a little daunting at first, but they're worth the effort to understand, especially for problem spaces like assemblies and sub-assemblies.