Removing duplicate rows if value from 1 row exists in another row SQL

Question

I'm having an issue removing all the rows that have a certain value in them and then removing the other rows that have the same value as an already removed rows column. Here is an example of what I have right now:

SELECT Race.intRaceID, Register.intRegID, Member.intMemberID
FROM Race
LEFT JOIN Register ON Race.intRaceID=Register.intRaceID
LEFT JOIN Member ON Register.intMemberID=Member.intMemberID

which gives me:

+------------+-----------+-------------+
| intRaceID  | intRegID  | intMemberID |
+------------+-----------+-------------+
|       100  | 10        | 1           |
|       100  | 40        | 2           |
|       200  | NULL      | NULL        |
|       300  | 30        | 2           |
|       400  | 20        | 4           |
|       500  | NULL      | NULL        |
+------------+-----------+-------------+

So, what I'm attempting to do is remove a particular intMemberID (keeping the NULLs) and all of the intRaceID's they're associated with. I added

WHERE Member.intMemberID <> 2 OR Member.intMemberID IS NULL

Giving the result:

+------------+-----------+-------------+
| intRaceID  | intRegID  | intMemberID |
+------------+-----------+-------------+
|       100  | 10        | 1           |
|       200  | NULL      | NULL        |
|       400  | 20        | 4           |
|       500  | NULL      | NULL        |
+------------+-----------+-------------+

but that will not remove all intRaceIDs associated with the intMemberID. Any help would be greatly appreciated

The table I'm trying to show is this:

+------------+-----------+-------------+
| intRaceID  | intRegID  | intMemberID |
+------------+-----------+-------------+
|       200  | NULL      | NULL        |
|       400  | 20        | 4           |
|       500  | NULL      | NULL        |
+------------+-----------+-------------+

intRaceID is on two rows: with intMemberID=1 and =2. So if you exclude intMemberID=2 you still keep intRaceID=100. BTW Why are you talking about remove while you are doing a SELECT query? Are you talking about exclude from select? — Lelio Faieta, May 27 '15 at 13:06
yes, sorry, I do mean excluding the whole row from the select. I'll edit my post to show my desired table — savl0n, May 27 '15 at 13:21
possible duplicate of [Find duplicate records in MySQL](http://stackoverflow.com/questions/854128/find-duplicate-records-in-mysql) — Dan, May 27 '15 at 13:23

score 0 · Answer 1 · answered May 27 '15 at 13:31

I think you have to write the WHERE clause like in the following query:

SELECT Race.intRaceID, Register.intRegID, Member.intMemberID
FROM Race
LEFT JOIN Register ON Race.intRaceID = Register.intRaceID
LEFT JOIN Member ON Register.intMemberID = Member.intMemberID
WHERE Race.intRaceID NOT IN (
   SELECT Race.intRaceID
   FROM Race 
   INNER JOIN Register ON Race.intRaceID = Register.intRaceID
   INNER JOIN Member ON Register.intMemberID = Member.intMemberID
   WHERE Member.intMemberID = 2)
ORDER BY intRaceID;

This way you exclude all records for which intRaceID is related to intMemberID with a value of 2.

Demo here

Removing duplicate rows if value from 1 row exists in another row SQL

1 Answers1