query find duplicates with only certain criteria

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

query find duplicates with only certain criteria

Author

Topic

thr33xx
Starting Member

6 Posts

Posted - 2011-07-29 : 13:47:25

I realize this topic is discussed daily, but being absolutely new to sql, I am hoping someone can help me with my exact issue and explain what I need to do.

I have a single table which I am trying to find duplicates which appear in a single column (pat_id). The criteria I am searching by are three additional columns - (study_id, event_code, event_result).

What I have so far is this - Created a query that meets the criteria of the data I am looking for. The problem I am having is then finding which of the records which meets my criteria is a duplicate for the (pat_id).

The query I have written so far is this -

SELECT *
FROM dbo.asys_EVENTS
WHERE (STUDY_ID = '1234') AND (EVENT_CODE = '280') AND (EVENT_RESULT = '1462')
(SELECT pat_id
FROM dbo.asys_events
GROUP BY pat_id
HAVING (COUNT(pat_id) > 1))

Again, I am absolutely new to SQL, so any help is more than appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-29 : 13:58:22

[code]
SELECT *
FROM dbo.asys_EVENTS
WHERE (STUDY_ID = '1234') AND (EVENT_CODE = '280') AND (EVENT_RESULT = '1462')
AND pat_id IN
(SELECT pat_id
FROM dbo.asys_events
GROUP BY pat_id
HAVING (COUNT(pat_id) > 1))
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

thr33xx
Starting Member

6 Posts

Posted - 2011-07-29 : 14:20:16

Hmm.

It's yielding all records, same as if I had used the query,

SELECT *
FROM dbo.asys_EVENTS
WHERE (STUDY_ID = '1234') AND (EVENT_CODE = '280') AND (EVENT_RESULT = '1462')

I know for a fact that at least 2 duplicate entries exist (confirmed visually looking). Am I missing something else?

quote:
Originally posted by visakh16

SELECT *
FROM dbo.asys_EVENTS
WHERE (STUDY_ID = '1234') AND (EVENT_CODE = '280') AND (EVENT_RESULT = '1462')
AND pat_id IN
(SELECT pat_id
FROM dbo.asys_events
GROUP BY pat_id
HAVING (COUNT(pat_id) > 1))

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-29 : 14:25:34

instead of pat_id itself give some unique valued column inside count.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-29 : 14:30:17

what do you mean by yielding all records? can you show some sample data and then explain what you want out of them as output?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

thr33xx
Starting Member

6 Posts

Posted - 2011-07-29 : 14:54:59

By yielding all records, I mean this query (mine)-

SELECT *
FROM dbo.asys_EVENTS
WHERE (STUDY_ID = '1234') AND (EVENT_CODE = '280') AND (EVENT_RESULT = '1462')
(SELECT pat_id
FROM dbo.asys_events
GROUP BY pat_id
HAVING (COUNT(pat_id) > 1))

provides the same results as yours

SELECT *
FROM dbo.asys_EVENTS
WHERE (STUDY_ID = '1234') AND (EVENT_CODE = '280') AND (EVENT_RESULT = '1462')
AND pat_id IN
(SELECT pat_id
FROM dbo.asys_events
GROUP BY pat_id
HAVING (COUNT(pat_id) > 1))

What I would like it to do is take my results, and only show the records in which the pat_id value is a duplicate within the table.

Ghanta
Yak Posting Veteran

96 Posts

Posted - 2011-07-29 : 15:11:37

is this what u want..

SELECT *
FROM dbo.asys_EVENTS
WHERE (STUDY_ID = '1234') AND (EVENT_CODE = '280') AND (EVENT_RESULT = '1462')
AND pat_id IN
(SELECT pat_id
FROM dbo.asys_events
where (STUDY_ID = '1234') AND (EVENT_CODE = '280') AND (EVENT_RESULT = '1462')
GROUP BY pat_id
HAVING (COUNT(pat_id) > 1))

thr33xx
Starting Member

6 Posts

Posted - 2011-07-29 : 15:54:18

YES!

Thank you very much!

quote:
Originally posted by Ghanta

is this what u want..

SELECT *
FROM dbo.asys_EVENTS
WHERE (STUDY_ID = '1234') AND (EVENT_CODE = '280') AND (EVENT_RESULT = '1462')
AND pat_id IN
(SELECT pat_id
FROM dbo.asys_events
where (STUDY_ID = '1234') AND (EVENT_CODE = '280') AND (EVENT_RESULT = '1462')
GROUP BY pat_id
HAVING (COUNT(pat_id) > 1))

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources