Combining 2 complex queries in SQL Server

user_12879474 0
Viewed 313

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:

enter image description here

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:

enter image description here

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?

union select sql-server sql
5 Answers

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%'
user_86516010

I think this query can work:

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, aux.t_pric1
FROM cte
LEFT JOIN ttcibd001201 dsc ON dsc.t_item = cte.secondaryArt 
LEFT JOIN (SELECT DISTINCT t_orno, t_item, FIRST_VALUE(t_pric) OVER (PARTITION BY t_orno, t_item ORDER BY t_ddte DESC) AS t_pric1 FROM ttdpur401201) AS aux ON aux.t_item = cte.secondaryArt
WHERE TRIM(secondaryArt) LIKE N'30%'
user_154242270

I'm not sure if i exactly know what you want, but i would try something like

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
left join ( 
    select 
     t_orno, 
     t_item, 
     t_pric 
    from ttdpur401201 
    group by 
     t_orno, 
     t_item, 
     t_price 
    order by t_ddte desc
) i on i.t_item = cte.secondaryArt
where trim(secondaryArt) like N'30%'
user_74543360

Please try this:

WITH cte1(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 cte1
                        ON cte1.secondaryart = ttibom010201.t_mitm),
     cte2
     AS (SELECT TOP 1 t_orno,
                      t_item,
                      t_pric
         FROM   ttdpur401201
         WHERE  t_item LIKE '%30102024%'
         ORDER  BY t_ddte DESC)
SELECT cte1.*,
       dsc.t_cuni,
       dsc.t_dsca,
       cte2.t_pric
FROM   cte1
       LEFT JOIN ttcibd001201 dsc
              ON dsc.t_item = cte1.secondaryart
       LEFT JOIN cte2
              ON cte1.secondaryart = cte2.t_item
WHERE  Trim(secondaryart) LIKE N'30%'

Expected result: The above should add an extra column t_pric to your original query as follows:

t_pric
NULL
NULL
1.4
1.4
user_10637160

I'm not sure of the cardinality of ttibom010201 and ttdpur401201 but you could potentially join the ttdpur401201 table in the CTE to get the price.

with cte(MainArt,secondaryArt, Quantity, Price) as (
SELECT t_mitm, t_sitm, t_qana, t_pric
            FROM ttibom010201 b join ttdpur401201 p ON b.t_sitm = p.t_item 
)

The same applies for the second half of the CTE query.

user_27323710
Related