CODEWITHSUNDEEP
sqloracle-database
Remi Kapler
Remi Kapler

Reputation: 11

SQL condition for a join

I've created an SQL query (Oracle) that is a join of 2 tables, and this table has entries related to users joining or leaving a particular instance.

So, let's say that the table looks something like: 

+------------+--------+
| User_ID    | State  |
+------------+--------+
| 101        | Joined |
+------------+--------+
| 102        | Joined |
+------------+--------+
| 101        |   Left |
+------------+--------+

As you can see, user 101 Joined, but then left. Is it possible to somehow specify that in case user left, then all entries related to this user are removed from the table (user ID is unique), but in case there is only entry saying that user Joined, then user will stay in the table?

I am new to SQL, my apologies if the question lacks some details, please feel free to ask for clarification.

Upvotes: 1

Views: 69

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

You can do something like this:

select t.*
from t
where not exists (select 1 from t t2 where t2.id = t.id and t.state = 'Left');

You can easily turn this to a delete:

delete t
        where exists (select 1 from t t2 where t2.id = t.id and t.state = 'Left');

Upvotes: 0

Cid
Cid

Reputation: 15247

You can get the list of ID that has the State "Left" and then delete those IDs

DELETE FROM tablename 
WHERE  user_id IN (SELECT user_id 
                   FROM   tablename 
                   WHERE  state = "left")

Upvotes: 1

David Faber
David Faber

Reputation: 12485

To remove the records pertaining to users that have left, you might try the following:

DELETE FROM mytable m1
 WHERE EXISTS ( SELECT 1 FROM mytable m2
                 WHERE m2.user_id = m1.user_id
                   AND m2.state = 'Left' );

Alternately (this might make things a bit more clear):

DELETE FROM mytable m1
 WHERE m1.user_id IN ( SELECT m2.user_id
                         FROM mytable m2
                        WHERE m2.state = 'Left' );

Hope this helps.

Upvotes: 2

Related Questions