Solved

Select Statement that Removes Duplicates

Posted on 2011-02-23
6
196 Views
Last Modified: 2012-05-11
Say I've got the following table...

ID1, ID2, ID3, Name
1111, 2222, 3333, Andy
4444, 5555, 6666, Brian
4444, 5555, 6666, Chris
7777, 8888, 9999, Earl
7777, 8888, 9999, Frank

...and suppose the name Brian is entered into a textbox in a form. I need to write a query that returns the following result:

1111, 2222, 3333, Andy
4444, 5555, 6666, Brian
7777, 8888, 9999, Earl
7777, 8888, 9999, Frank

Essentially, I need to get rid of records with the same values in the first 3 fields only when one of the duplicate records contains the name Brian.

This is being developed in Access.
0
Comment
Question by:ltdanp22
  • 3
  • 2
6 Comments
 
LVL 26

Expert Comment

by:tigin44
ID: 34964971
try this
SELECT ID1, ID2, ID3, Name
FROM yourTable T
WHERE NOT EXISTS
	INNER JOIN (SELECT ID1, ID2, ID3, 
				FROM yourTable
				WHERE NAME = 'Brian' AND T.ID1 = A.ID1 AND T.ID2 = A.ID2 AND T.ID3 = A.ID3)
UNION 

SELECT ID1, ID2, ID3, 'Brian' AS Name
FROM yourTable T
	INNER JOIN (SELECT ID1, ID2, ID3, 
				FROM yourTable
				WHERE NAME = 'Brian') A ON T.ID1 = A.ID1 AND T.ID2 = A.ID2 AND T.ID3 = A.ID3

Open in new window

0
 
LVL 8

Expert Comment

by:rushShah
ID: 34967080
try this,


DECLARE @TempTable TABLE(ID1 INT, ID2 INT, ID3 INT)
INSERT INTO @TempTable
SELECT	t1.ID1, t1.ID2, t1.ID3
FROM	YourTable t1
WHERE	t1.Name='Brian'

SELECT	ID1, ID2, ID3, Name
FROM	YourTable
EXCEPT
SELECT	t.ID1, t.ID2, t.ID3, t.Name
FROM	YourTable t
INNER JOIN @TempTable t1 ON t1.ID1=t.Id1 AND t1.ID2=t.ID2 AND t1.ID3=t.ID3
WHERE	t.Name <> 'Brian'

Open in new window

0
 

Author Comment

by:ltdanp22
ID: 34979930
Hi guys,

Can you explain what these queries are doing? I'm trying to incorporate them and I'm getting errors. Would be easier to debug if I knew what these queries were trying to do.

tigin. Is this right? Doesn't make sense to me.

WHERE NOT EXISTS
      INNER JOIN

rush, are these two separate queries that need to be called one after the next? What's the scope and lifetime of @TempTable? Is it available when I call the second query?
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 8

Expert Comment

by:rushShah
ID: 34981915
ltdanp22:

You can combine into one query as well.
try this,


SELECT	ID1, ID2, ID3, Name
FROM	YourTable
EXCEPT
SELECT	t.ID1, t.ID2, t.ID3, t.Name
FROM	YourTable t
INNER JOIN (SELECT	ID1, ID2, ID3
FROM	YourTable
WHERE	Name='Brian') t1 ON t1.ID1=t.Id1 AND t1.ID2=t.ID2 AND t1.ID3=t.ID3
WHERE	t.Name <> 'Brian'

Open in new window

0
 

Author Comment

by:ltdanp22
ID: 34997866
Rush,

This query...

   SELECT t.ID1, t.ID2, t.ID3, t.Name
   FROM YourTable t INNER JOIN
   (
      SELECT ID1, ID2, ID3
      FROM YourTable
      WHERE Name='Brian'
   ) t1 ON t1.ID1=t.Id1 AND t1.ID2=t.ID2 AND t1.ID3=t.ID3
   WHERE t.Name <> 'Brian'

...returns...

4444, 5555, 6666, Chris

...as expected.

But this query...

SELECT ID1, ID2, ID3, Name
FROM YourTable
WHERE NOT EXISTS (
   SELECT t.ID1, t.ID2, t.ID3, t.Name
   FROM YourTable t INNER JOIN
   (
      SELECT ID1, ID2, ID3
      FROM YourTable
      WHERE Name='Brian'
   ) t1 ON t1.ID1=t.Id1 AND t1.ID2=t.ID2 AND t1.ID3=t.ID3
   WHERE t.Name <> 'Brian'
)

...returns no records. Note that I had to replace EXCEPT with NOT EXISTS as I'm using Access 2007.

Can you see why the query isn't returning any results.
0
 
LVL 8

Accepted Solution

by:
rushShah earned 500 total points
ID: 34998494
ok..
try this,


SELECT ID1, ID2, ID3, Name
FROM YourTable AS main
WHERE NOT EXISTS (
   SELECT t.ID1, t.ID2, t.ID3, t.Name
   FROM YourTable t INNER JOIN
   (
      SELECT ID1, ID2, ID3
      FROM YourTable
      WHERE Name='Brian'
   ) t1 ON t1.ID1=t.Id1 AND t1.ID2=t.ID2 AND t1.ID3=t.ID3
   WHERE t.Name <> 'Brian' AND main.Name=t.Name
)

Open in new window

0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
STDEVP in SQL 2 58
SQL Query to display duplicates ? 6 39
Updating ms sql with special characters 8 41
Query Peformance + mulitple query plans 9 48
As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now