Combining 2 complex queries in SQL Server

Question

First of all, I cannot provide a SQL Fiddle because data is really complex to be recreated. I will try to present the queries, outputs, and questions as clear as possible.

I have query that looks-like this:

with cte(MainArt,secondaryArt, Quantity) as(
SELECT t_mitm, t_sitm, t_qana
            FROM ttibom010201 
        WHERE TRIM(t_mitm) = 'ACL-4812070395-4'
union all
select 
ttibom010201.t_mitm, ttibom010201.t_sitm, ttibom010201.t_qana
from ttibom010201 inner join cte on cte.secondaryArt=ttibom010201.t_mitm
        )
select    cte.* ,dsc.t_cuni, dsc.t_dsca from cte
left join ttcibd001201 dsc on dsc.t_item = cte.secondaryArt 
where TRIM(secondaryArt)  like N'30%'

This exact query returns me these 4 rows:

From a different table I use this secondArt value to get some info:

select top 1 t_orno, t_item,t_pric from ttdpur401201 where t_item like '%30102024%' order by  t_ddte desc

The result is:

As you can see t_item and secondaryArt are the same (3rd and 4th row from the first query). So I want to be able somehow to merge these 2 queries into 1. In the first table, I only need to add this t_pric.

How can I merge these two queries?

Answer 1

Just a left join between two queries will do the trick.

  with cte(MainArt,secondaryArt, Quantity) as(
SELECT t_mitm, t_sitm, t_qana
            FROM ttibom010201 
        WHERE TRIM(t_mitm) = 'ACL-4812070395-4'
union all
select 
ttibom010201.t_mitm, ttibom010201.t_sitm, ttibom010201.t_qana
from ttibom010201 inner join cte on cte.secondaryArt=ttibom010201.t_mitm
        )
select    cte.* ,dsc.t_cuni, dsc.t_dsca, t_pric from cte
left join ttcibd001201 dsc on dsc.t_item = cte.secondaryArt 
Left join (select  t_orno, t_item,t_pric from ttdpur401201 inner join cte on t_item like cte.secondaryArt order by  t_ddte desc) t
on cte.secondaryArt = t.t_item
where TRIM(secondaryArt)  like N'30%'

New modified answer:

with cte(MainArt,secondaryArt, Quantity) as(
SELECT t_mitm, t_sitm, t_qana
            FROM ttibom010201 
        WHERE TRIM(t_mitm) = 'ACL-4812070395-4'
union all
select 
ttibom010201.t_mitm, ttibom010201.t_sitm, ttibom010201.t_qana
from ttibom010201 inner join cte on cte.secondaryArt=ttibom010201.t_mitm
        )
select    cte.* ,dsc.t_cuni, dsc.t_dsca, t_pric from cte
left join ttcibd001201 dsc on dsc.t_item = cte.secondaryArt 
Left join (select top 1 t_orno, t_item,t_pric,row_number()over(partition by t_item order by t_ddte desc) rnfrom ttdpur401201 ) t
on t.t_item like cte.secondaryArt  and t.rn=1
where TRIM(secondaryArt)  like N'30%'