SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 match two columns of table1 with table2
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

learning_grsql
Posting Yak Master

230 Posts

Posted - 02/01/2014 :  08:12:13  Show Profile  Reply with Quote
Hi, I have two tables with similar two columns as shown below

table1
code | organisation
256 | abc
832 | xyz
893 | tax
921 | abc
951 | abc

table2
code | organisation
951 | abc
832 | xyz
256 | abc
893 | tax
921 | tax



Now, I want to check whether all the codes in table1 existing in table2 and list them, and if both columns from table1 is matching with the both columns in table2. For e.g. 256|abc in table1 is matching with 256|abc in table2

The output should be :

921 | tax

For e.g. 256abc is there or not in table2,

khtan
In (Som, Ni, Yak)

Singapore
17587 Posts

Posted - 02/01/2014 :  09:28:40  Show Profile  Reply with Quote
what do you want ?

rows in table1 that does not exists in table2 ?

select *
from   table1 t1
where  not exists
       (
           select *
           from   table2 t2
           where  t2.code = t1.code
           and    t2.organisation = t1.organisation



KH
Time is always against us

Go to Top of Page

learning_grsql
Posting Yak Master

230 Posts

Posted - 02/01/2014 :  12:49:58  Show Profile  Reply with Quote
Thanks It works
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 02/02/2014 :  01:45:56  Show Profile  Reply with Quote
quote:
Originally posted by khtan

what do you want ?

rows in table1 that does not exists in table2 ?

select *
from   table1 t1
where  not exists
       (
           select *
           from   table2 t2
           where  t2.code = t1.code
           and    t2.organisation = t1.organisation



KH
Time is always against us




shouldnt it be the other way around seeing the sample output?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

learning_grsql
Posting Yak Master

230 Posts

Posted - 02/02/2014 :  07:28:47  Show Profile  Reply with Quote
HI Visakh, you are correct. But after I went through Khtan's code, I got an idea how this code works so I can use it as I want.

Edited by - learning_grsql on 02/02/2014 07:29:32
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 02/02/2014 :  11:57:23  Show Profile  Reply with Quote
quote:
Originally posted by learning_grsql

HI Visakh, you are correct. But after I went through Khtan's code, I got an idea how this code works so I can use it as I want.


Ok good
just checked as I couldnt correlate your sample output with suggestion

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000