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

Question

4.00/5 (1 vote)

See more:

SQL

--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

SQL

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

SQL

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

SQL

--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

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.

Posted 12-Jun-13 21:37pm

jhon123 2

Updated 12-Jun-13 21:44pm

gvprabu

v2

Add a Solution

Comments

E.F. Nijboer 13-Jun-13 4:06am

You have all the condition defined and now you only need to hire someone with sql skills...
What have you tried yourself? Have some sql you tried?

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

sanchit patne · Answer 1 · 2013-06-13T00:03:00

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

SQL

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

Savalia Manoj M · Answer 2 · 2013-06-13T03:26:00

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

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

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0