Answers.Body: <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>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-11-15T19:05:02.217Z
Answers.Text: 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.
Answers.UserDisplayName: Brad Bell
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-11-15T09:53:17.400Z
Answers.LastActivityDate: 2017-11-15T09:53:17.400Z
Answers.UserDisplayName: Steven Grant
Body: <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>
  
Tags: <database><mysql><query>
Title: Pruning the users table
ThreadText: # 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.  
  
  ---
  
  