Testei com um select dentro de uma consulta e deu certo, estou usando como base o conhecimento de excel, estão sendo bem uteis , para entender a logica.
Interpretei o exercício como criar uma procv que busca informação que deve procurar dentro de outra procv, os parentes () sequem a mesma logica ate o momento
select * from [ITENS NOTAS FISCAIS] where
[CODIGO DO PRODUTO] = (select [CODIGO DO PRODUTO] from [dbo].[TABELA DE PRODUTOS] where
[NOME DO PRODUTO] = 'Linha Refrescante - 1 Litro - Morango/Limão')
order by QUANTIDADE desc