Find duplicate in column tied to another column...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Find duplicate in column tied to another column...

Author

Topic

rod_farva_sql
Starting Member

2 Posts

Posted - 2015-04-29 : 15:47:57

Hi,
SQL newb here. Been banging my head on this for a couple of hours without luck. I have one table with two columns, GUID and PID and another table with three columns, GUID, LastName, and FirstName. For each unique PID, I need to find the PID's that have more than one GUID and then match that with their respective FirstName and LastName from the other table.

Table1

GUID PID
GUID1 PID1
GUID1 PID1
GUID1 PID1
GUID2 PID1
GUID3 PID2
GUID3 PID2
GUID3 PID2

Table2

GUID LastName FirstName
GUID1 Mulder Fox
GUID2 Scully Dana
GUID3 Skinner Walter

So I'm looking for a result like:

PID1 GUID 1 Mulder Fox
----- GUID 2 Scully Dana

Any help is appreciated!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-04-29 : 16:05:35

[code]
with c (PID, [GUID], PidGuidCount)
as
(
select PID, [GUID], count(*)
from Table1
group by PID, [GUID]
), p (PID)
as
(
select PID
from c
group by PID
having count(*) > 1
)
select distinct p.PID, Table1.[GUID], Table2.LastName, Table2.FirstName
from Table1
join Table2 on Table1.[GUID] = Table2.[GUID]
join p on Table1.PID = p.PID
[/code]

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-04-29 : 16:16:33

[code]
SELECT DISTINCT t2.GUID -- Outer query -- get final results
,t2.LastName
,t2.FirstName
FROM @table2 t2
INNER JOIN ( -- Inner query 1 -- get GUIDS from PIDS having >1 GUIDS
SELECT GUID
FROM @table1 t1
INNER JOIN ( -- Inner query 2 -- get PIDS having >1 GUIDS
SELECT PID
FROM @table1
GROUP BY pid
HAVING count(guid) > 1
) t1x ON t1x.PID = t1.PID
) t1 ON t1.GUID = t2.GUID
ORDER BY guid
[/code]

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-04-29 : 16:21:40

Another way:


SELECT DISTINCT t2.GUID
	,LastName
	,FirstName
FROM @table2 t2
CROSS APPLY (
	SELECT PID
		,GUID
		,count(GUID) OVER (PARTITION BY pid) Number_GUIDs
	FROM @table1 t1
	WHERE t1.guid = t1.GUID
	) _
WHERE Number_GUIDs > 1
ORDER BY LastName
	,FirstName

rod_farva_sql
Starting Member

2 Posts

Posted - 2015-04-30 : 12:08:23

I'm sorry, but the results are not what I'm expecting for any of these examples. I may not have explained it well enough. Let's start off simpler for now. Can I get a query that just shows which PID's have more than one unique GUID. A PID could have multiple GUID's that are the same, I'm looking for the PID's that have multiple GUID's that are different.

Table1

GUID PID
GUID1 PID1
GUID1 PID1
GUID1 PID1
GUID2 PID1
GUID3 PID2
GUID3 PID2
GUID3 PID2

The result of the query would only have PID1 because it has two unique GUID's. PID2 would not be listed has it has the same GUID3 in each row.

Result:
PID1

I appreciate everyones help and sorry for the confusion..

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-04-30 : 12:45:27

add the keyword DISTINCT to the COUNT(GUID) in the queries that were posted.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources