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
Solved

Select Statement that Removes Duplicates

Posted on 2011-02-23
6
198 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
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 
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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

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.…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

808 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