November 5, 2009 at 11:41 am
I am using SQL 2005.
I am trying to compare values between 2 tables.
I have two tables like example Table1 and Table2
create table Table1
(
col1 varchar(10), col2 varchar(10)
)
create table Table2
(
c1 varchar(10), c2 varchar(10)
)
insert into Table1 values('ABC','USA')
insert into Table1 values('XYZ','USA')
insert into Table2 values('ABC','USA')
Now I need a output query which will give the records which are present in Table1 but are not present in Table2
in this case it would be 'XYZ','USA' since 'ABC','USA' is also present in Table2.
Please note while comparing values I am looking at the combination of the two columns in each table.
Thanks.
November 5, 2009 at 11:52 am
Something like this should work.
create table Table1
(
col1 varchar(10), col2 varchar(10)
)
create table Table2
(
c1 varchar(10), c2 varchar(10)
)
insert into Table1 values('ABC','USA')
insert into Table1 values('XYZ','USA')
insert into Table2 values('ABC','USA')
SELECT a.* FROM table1 a
LEFT JOIN table2 b ON a.col1 = b.c1 AND a.col2 = b.c2
WHERE b.c1 IS NULL
DROP TABLE table1, table2
November 5, 2009 at 11:55 am
You can also use Except for this. Look up the details in Books Online, but it's pretty easy to use.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply