查询来自两个表的读取匹配，不匹配和新添加的记录。 [英] query for read matched ,unmatched and newly added records from two tables.

问题描述

从两个表中查询读取匹配，不匹配和新添加的记录。

query for read matched ,unmatched and newly added records from two tables.

--Table1
District	Center		  s_No
Chittoor	VenkatagiriKota   20004
Hyderabad	CivilSupply	  20005 
Ananthapur	NGOHome 	  20006	
Ananthapur	Ananthapur        20007
Chittoor	Chittoor          20008
Ananthapur	NGOHome 	  20010	
Ananthapur	Ananthapur        20020

--Tble2
Id       S_No	
101	 20004
102      20005
103      20006 
104	 20007
105      20008

--table3
id	s_No	Name
101	20004	aaa
104	20007	bbb
107	20010   xxx
108	20020	yyy

--O/P;
District      Center              S_No         Id      Status                        
Chittoor      VenkatagiriKota     20004	       101     Matched
Hyderabad     CivilSupply	  20005	       102     Un_matched
Ananthapur    NGOHome		  20006	       103     un_matched 
Ananthapur    Ananthapur          20007        104     Matched
Chittoor      Chittoor 	          20008	       105     Un_matched
Ananthapur    NGOHome             20010        107     Newly added
                                  20020        108     Newly added

匹配意味着：如果Table2和Table3记录匹配状态匹配

不匹配意味着：如果Table2和Table3记录在不匹配状态下是不匹配的状态

新添加：如果在table3中添加了新记录，则新添加状态。

Matched means:if Table2 and Table3 record is matched status in matched
Unmatched means:if Table2 and Table3 record is unmatched status in unmatched
Newly added:if new record is added in table3 status is newly added.

推荐答案

尝试以下查询..............请告诉我这是否有帮助。

Try below query..............please Let me know if this helps.

with cte as (select t1.District,t1.Center,t3.s_No,t3.id,case isnull(t2.id,0) when 0 then 'Newly added' else 'Matched' end as status
from Table_2 t2 right outer join Table_3 t3 on t2.S_No = t3.s_No
inner join Table_1 t1 on t3.S_No = t1.s_No)

select t1.District,t1.Center,t1.s_No,isnull(cte.status,'Un_matched') as Status from Table_1 t1 left outer join cte on t1.s_No = cte.s_No

请试试这个...

Please try this...

Select table1.District,table1.Center,table1.S_No ,
		Case when (table1.s_no = table2.s_no AND table2.s_no = table3.s_no ) then 'Matched'
		When (table2.s_no Is NULL AND table3.s_no IS NULL ) then 'Newly added'
		Else 'Un_matched'		 
	End as Status
  from 
		table1 Left join
		table2  on  table2.s_no = table1.s_no
		left join table3 on table2.s_no = table3.s_no

这篇关于查询来自两个表的读取匹配，不匹配和新添加的记录。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！