<p>Looks like this will do the trick</p>

<pre><code>select * from craft_users
left join craft_entries on craft_users.id = craft_entries.authorId
where craft_entries.authorId is null and craft_users.id &lt;&gt; "1"
</code></pre>

CC BY-SA 3.0
2017-11-15T19:05:02.217Z
You probably don't want to delete the results of that query from the `craft_users` table, though.  Take the `id` from the results and delete the matching `id`s from the `craft_elements` table.  The cascading deletes on the foreign keys will take of cleanup up the relevant rows in the related tables.
Brad Bell
CC BY-SA 3.0
2017-11-15T09:53:17.400Z
2017-11-15T09:53:17.400Z
Steven Grant
<p>I have a site that deals exclusively with user generated content.</p>

<p>After a few years, the users table is getting to a stage where it can probably be pruned of folk who have registered but not actually submitted any content.</p>

<p>What query would I run to show the users who have zero posts?</p>

<p>I thought something like <code>select id from craft_users where id not in (select distinct authorId from craft_entries)</code> would do it.</p>

<p>The <code>distinct</code> query certainly returns 1020 rows where we actually have around 1800 users.</p>

<database><mysql><query>
Pruning the users table
# Pruning the users table

(Asked by: username_0 on 2017-11-15)

I have a site that deals exclusively with user generated content.

After a few years, the users table is getting to a stage where it can probably be pruned of folk who have registered but not actually submitted any content.

What query would I run to show the users who have zero posts?

I thought something like `select id from craft_users where id not in (select distinct authorId from craft_entries)` would do it.

The `distinct` query certainly returns 1020 rows where we actually have around 1800 users.

**Tags:** database, mysql, query


---

## 1 answers

### Answer by username_0 on 2017-11-15. Score: 1

Looks like this will do the trick
select * from craft_users
left join craft_entries on craft_users.id = craft_entries.authorId
where craft_entries.authorId is null and craft_users.id <> "1"

> **Comments:**
> **username_1**: You probably don't want to delete the results of that query from the `craft_users` table, though.  Take the `id` from the results and delete the matching `id`s from the `craft_elements` table.  The cascading deletes on the foreign keys will take of cleanup up the relevant rows in the related tables.  

---

