If the combination of col1 and col2 make up a composite primary key in
both tables (meaning they can only show up once in each table) then:

SELECT table1.col1,table1.col2
from table1
join table2
on table1.col1=table2.col1 and
table1.col2=table2.col2

If the col1/col2 combination can show up multiple times in either table
then:

SELECT col1,col2
from (SELECT col1,col2
from table1
UNION ALL
SELECT col1,col2
from table2) tbl3
group by col1,col2
having count(1) > 1


Trevor


----- Original Message -----
From: Mugasundaram Shan <mssqldba-ezmlmshield-
x18645145.[Email address protected]
Date: Friday, March 31, 2006 9:43 am
Subject: compare table
To: LazyDBA Discussion <[Email address protected]

> Hi gurus
>
>
>
> I need to compare first 2 columns of two table and output only those
> duplicate rows. How can I do that
>
>
>
> Thanks
>
>
>
>
>
>
> "The information in this e-mail and in any attachments is
> confidential and intended solely
> for the attention and use of the named addressee(s). This
> information may be subject to legal,
> professional or other privilege and further distribution of it is
> strictly prohibited without
> our authority. If you are not the intended recipient, you are not
> authorised to and must not
> disclose, copy, distribute, or retain this message or any part of
> it, and should notify us
> immediately.
>
> This footnote also confirms that this email has been automatically
> scanned for the presence
> of computer viruses, profanities and certain file types."
>
>
> -------------------------------------------------------------------
> --
> TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> Website : http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
>

MS Sql Server LazyDBA home page