Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Select Statement that Removes Duplicates

Posted on 2011-02-23
6
Medium Priority
?
203 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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 2000 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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

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.…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Suggested Courses

927 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