SQL query: Delete a entry which is not present in a join table?

I’m going to delete all users which has no subscription but I seem to run into problems each time I try to detect the users.

My schemas look like this:

Users = {userid, name}

Subscriptionoffering = {userid, subscriptionname}

Now, what I’m going to do is to delete all users in the user table there has a count of zero in the开发者_高级运维 subscriptionoffering table. Or said in other words: All users which userid is not present in the subscriptionoffering table. I’ve tried with different queries but with no result.

I’ve tried to say where user.userid <> subscriptionoffering.userid, but that doesn’t seem to work. Do anyone know how to create the correct query?

Thanks

Mestika

delete from Users 
where UserID not in
    (select userid from subscriptionOffering)

You can use a multi-table delete statement with a left outer join and focus on the non-matching rows like this:

delete u from Users as u
left outer join Subscriptionoffering as so
on so.userid = u.userid
where so.userid is null;

Here is some test code to prove it:

mysql> create table Users (userid int unsigned primary key auto_increment) engine = innodb;
Query OK, 0 rows affected (0.43 sec)

mysql> create table Subscriptionoffering (userid int unsigned not null, subscriptionname varchar(32) not null, foreign key (userid) references Users(userid)) engine = innodb;
Query OK, 0 rows affected (0.41 sec)

mysql> insert into Users () values (), (), (), (), ();
Query OK, 5 rows affected (0.38 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from Users;
+--------+
| userid |
+--------+
|      1 |
|      2 |
|      3 |
|      4 |
|      5 |
+--------+
5 rows in set (0.00 sec)

mysql> insert into Subscriptionoffering (userid, subscriptionname) values (1, 'One'), (3, 'Three'), (5, 'Five');
Query OK, 3 rows affected (0.31 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from Subscriptionoffering;
+--------+------------------+
| userid | subscriptionname |
+--------+------------------+
|      1 | One              |
|      3 | Three            |
|      5 | Five             |
+--------+------------------+
3 rows in set (0.00 sec)

mysql> delete u from Users as u
    -> left outer join Subscriptionoffering as so
    -> on so.userid = u.userid
    -> where so.userid is null;
Query OK, 2 rows affected (0.36 sec)

mysql> select * from Users;
+--------+
| userid |
+--------+
|      1 |
|      3 |
|      5 |
+--------+
3 rows in set (0.00 sec)