Select Statement that Removes Duplicates

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.
ltdanp22Asked:
Who is Participating?
 
rushShahConnect With a Mentor Commented:
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
 
tigin44Commented:
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
 
rushShahCommented:
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
ltdanp22Author Commented:
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
 
rushShahCommented:
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
 
ltdanp22Author Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.