I have 2 tables
DistinctVend and NonRefvend. Both of them have the VendorID column.

The DistinctVend has 25000 records
The NonRefvend table has 26000 records.

I want to run a query that will select only the records from NonRefVend
table where the Vendorid is also in the DistinctVend table.

Furthermore I want to be able to document the vendorid's that are in
variance in the two tables.

Could someone assist please.

Thanks
S Commar
s_commar (AT) hotmail (DOT) com

Hopefully this is what you are looking for:


/*
Vendorid exists in both tables
*/
SELECT A.*
FROM NonRefVend A
JOIN DistinctVend B ON A.Vendorid = B.VendorID

/*
Vendorid only in NonRefVend
*/
SELECT A.*
FROM NonRefVend A
WHERE NOT EXISTS (SELECT * FROM DistinctVend B WHERE A.Vendorid =
B.VendorID)

/*
Vendorid only in DistinctVend
*/
SELECT A.*
FROM DistinctVend A
WHERE NOT EXISTS (SELECT * FROM NonRefVend B WHERE A.Vendorid = B.VendorID)



--
Keith


"Sam" <s_commar (AT) hotmail (DOT) com> wrote

SELECT a.VendorID
FROM NonRefVend AS a
WHERE EXISTS
(
SELECT 1
FROM DistinctVend AS b
WHERE b.VendorID = a.VendorID
)

This will give you a list of all vendor ids from NonRefVend that exist in
DistinctVend table.

Regarding your second request, could you please elaborate pn 'variance'?
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @ http://vyaskn.tripod.com/


"Sam" <s_commar (AT) hotmail (DOT) com> wrote

Hi

Thanks for your help. I wanted to get a list of all the vendorid that do
not match in both the tables.

Thanks
S Commar


*** Sent via Developersdex http://www.developersdex.com ***

Have you checked Keith's response?
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @ http://vyaskn.tripod.com/


"Sandeep Commar" <s_commar (AT) hotmail (DOT) com> wrote