将子查询添加到cte以从两个表中获取结果 [英] Add subquery to cte to get results from two tables

问题描述

declare @nodeid int = '1';
with cte as ( 
select  cust_ID, name,null lnode, null rnode from  user_detail where 
cust_ID = @nodeid 

union all select t.cust_ID,t.name, ISNULL(cte.lnode, CASE WHEN t.joinside = 
0 THEN 1 ELSE 0 END) 
lnode, ISNULL(cte.rnode, CASE WHEN t.joinside = 1 THEN 1 ELSE 0 END) 
rnode from  user_detail t inner join cte on cte.cust_ID = t.parentid )
select cust_ID,name from cte where rnode='0' option (maxrecursion 0)

当前情景：



cust_id名称

1 Harry

5 Ravi

7 Ram

9 Tommy

10 Romi



我想要的是：



cust_id名称状态（分期付款表）

1 Harry支付
5 Ravi未支付

7 Ram未支付

9 Tommy支付

10支付Romi



现在我将解释我想要的：上面的查询仅从user_detail表获得结果。现在我想以这样的方式修改查询：它还将在分期表中搜索相应cust_id的状态列（上述查询返回）。



修改后的查询将根据cust_id从分期付款表中获取状态值。



查询将在第三列显示结果显示在第二个截图中。



i对cte和嵌套查询不太熟悉。我希望你们能理解我的问题。



我的尝试：



i不熟悉cte和嵌套的子查询

Current Scenario:

cust_id name
1 Harry
5 Ravi
7 Ram
9 Tommy
10 Romi

What i want is:

cust_id name status(installments table)
1 Harry paid
5 Ravi not paid
7 Ram not paid
9 Tommy paid
10 Romi paid

now i will explain what i want: the above query is getting results only from user_detail table. now i want to modify the query in such a way that it will also search in "installments" table for the "status" column of the respective cust_id (which the above query is returning).

The modified query will get the value of status from installments table based on cust_id.

And query will show the results in the third column as showing in 2nd screenshot.

i am not much familiar with cte and nested queries. I hope you guys will understand my problem.

What I have tried:

i am not familiar with cte and nested sub queries

推荐答案

select t.cust_ID,t.name, (CASE ISNULL(cte.lnode, 0) WHEN 1 THEN 'paid' ELSE 'not paid' END) lnode, (CASE ISNULL(cte.rnode, 0) WHEN 1 THEN 'paid' ELSE 'not paid' END)
rnode 
from  user_detail t 
      left join cte on cte.cust_ID = t.parentid 
where user_detail.cust_ID = 'YOUR_ID'

这篇关于将子查询添加到cte以从两个表中获取结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！