Solved

Select Statement that Removes Duplicates

Posted on 2011-02-23
6
200 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

Question has a verified solution.

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

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Suggested Courses

739 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