Need t-sql to extract duplicate values in a field

[sk8er1]

I have to create a report that looks for duplicate values in a particular field. For instance...

Loads VendorInvNo TransDate
----- ----------- ---------
11001 123 4/1/2005
11002 543 4/2/2005
11003 543 4/12/2005
11004 873 4/15/2005
11005 598 4/11/2005

I need to report on the duplicate values in the vendorInvNo field. So, how would I write the t-sql to extract those VendInvNo items that contain the same value.

Thanks

 

[vidru]

If, from the data above, you want the records with duplicate VendorInvNo's (i.e. the second and third records), then this query would get it:

SELECT L.*
FROM Loads L (guessing here)
JOIN
(SELECT VendorInvNo
FROM Loads
GROUP BY VendorInvNo HAVING COUNT(VendorInvNo) > 1
) M ON L.VendorInvNo= M.VendorInvNo

 

[sk8er1]

Thanks for getting back to me...

SELECT L.*
FROM Loads L (guessing here)
JOIN
(SELECT VendorInvNo
FROM Loads
GROUP BY VendorInvNo HAVING COUNT(VendorInvNo) > 1
) M ON L.VendorInvNo= M.VendorInvNo

I don't understand where is the "M" is coming from.
You have L for the Loads....I am trying it on Northwind orders table with one of the customerid's having dupe values.

 

[sk8er1]

I am trying to get it to work in Northwind Orders table...

SELECT *
FROM Orders L
JOIN
(SELECT CustomerID
FROM Orders
GROUP BY CustomerID HAVING COUNT(CustomerID) > 1
) /* don't know what to put here */

 

[vidru]

The 'M' is the alias for the derived table, and is used to link the results of the derived table back to the main table.

I don't have the Northwind SQL Server db, so I just DTS'd the Access version into SQL Server. I doubt the databases are exactly alike. In the Orders table, I have a total of 830 records. If I run this query...

SELECT L.*
FROM Orders L
JOIN
(SELECT CustomerID
FROM Orders
GROUP BY CustomerID HAVING COUNT(CustomerID) > 1
) M ON M.CustomerID = L.CustomerID

... I get 829 rows returned. That tells me that there is one customer that has only sent one order (CustomerID CENTC).

I'm still not clear on what exactly you're trying to extract. Do you only want a list of the ID's that appear more than once, or do you want all of the records that have an ID that is also in at least one other record?

-dave

 

[sk8er1]

In your original post you are correct that I want the records with duplicate VendorInvNo's (i.e. the second and third records), then this query would get it:

I was simply trying a variation of it on the orders table.

 

[sk8er1]

I got it to work ...its perfect thank you so so much.
You are the best. Can you recommend any good SQL books.

 

[vidru]

Have a look at SQLSister's faq for some recommendations: faq183-3324.

-dave