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
 Value Not Distinct		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

warrend
Starting Member

USA
15 Posts
Posted - 02/22/2013 :  09:58:43  Show Profile  Reply with Quote
I am using SQL 2005.

I have a table that gets populated with EDI information, so information should be unique to each LTL pro/tracking number. I found they are not. So I need to find the records with non-unique information. I'm completely stuck on how to get the results I want.

example table information
tbl_refnum carrier pronum
1021970 UPGF 678874420
1021970 CNWY 717916220

James K
Flowing Fount of Yak Knowledge

1518 Posts
Posted - 02/22/2013 :  10:05:58  Show Profile  Reply with Quote
For the example you posted, what makes it non-unique? Is it that the tbl_refnum is the same, butcarrier is different, or that pronum is different? Or is this a case where the data is unique because carrier is different? Not sure if this will work for you, but you might try this.
SELECT LTL FROM TheTable GROUP BY LTL HAVING COUNT(*) > 1
If that does not give you what you want, post some sample data that includes unique records and non-unique records and the expected output you want to see.
Go to Top of Page

warrend
Starting Member

USA
15 Posts
Posted - 02/22/2013 :  10:26:10  Show Profile  Reply with Quote
The tbl_refnum will be unique and the carrier EDI information will be unique. So in the example I gave, these records have the same tbl_refnum even though they should not because they are two different EDI shipments.
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2868 Posts
Posted - 02/22/2013 :  10:38:47  Show Profile  Reply with Quote
Please post some sample data that demonstrates what you are having trouble with. I can't tell from your posts even how many tables you're dealing with.

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

warrend
Starting Member

USA
15 Posts
Posted - 02/22/2013 :  10:46:52  Show Profile  Reply with Quote
It's one table.

So for this select example:

select *
from table1
where tbl_refnum = '1021970'

It is returning this:
tbl_refnum carrier pronum
1021970 UPGF 678874420
1021970 CNWY 717916220

But it should only return
tbl_refnum carrier pronum
1021970 UPGF 678874420
OR
tbl_refnum carrier pronum
1021970 CNWY 717916220
A unique record.

I need to find all those that have more than one pronum but the same tbl_refnum in the table.
Edited by - warrend on 02/22/2013 10:47:33
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2868 Posts
Posted - 02/22/2013 :  10:53:51  Show Profile  Reply with Quote
The James' query with a tweak should do it fot you
SELECT tbl_refnum
FROM table1
GROUP BY tbl_refnum
HAVING COUNT(DISTINCT pronum) > 1


Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

warrend
Starting Member

USA
15 Posts
Posted - 02/22/2013 :  11:18:13  Show Profile  Reply with Quote
I believe that did it! Thanks so much everyone.
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.11 seconds. Powered By: Snitz Forums 2000