Databases
StackOverflow2013
Postsdbo
POMySQL - Count Query With Table Join Filtering
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMySQL - Count Query With Table Join Filtering
    primarykey
    data
    text
    <p>I have is a users table, a posts table, a private table, and a user_stats table:</p> <pre><code>users user_id | user_name -------------------- 1 | tony 2 | steph 3 | lizzy 4 | adam posts post_id user_id sugg_by private (0 is public, 1 is private to authorized users) ----------------------------------------------------- 1 1 2 0 2 2 2 1 3 2 2 1 4 2 4 1 5 2 2 1 6 2 3 0 private private_id post_id authorized_user_id ----------------------------------------------- 1 2 4 2 2 3 3 4 4 4 5 1 5 5 3 user_stats user_id orig_posts_count(user_id=sugg_by in posts) sugg_posts_count(user_id&lt;&gt;sugg_by in posts) ----------------------------------------------------------------------------------------------------- 1 0 1 2 3 2 3 0 0 4 0 0 </code></pre> <p>The problem I'm having is in this query. In this example '4' is the $logged_in_id adam, and '2' is the user_id of the posts we are counting for steph. If steph is logged_in, the COUNT will not run and we will give 'posterview', this works. Back to any other logged in user or anyone logged out and the issue begins. So for adam '4':</p> <pre><code>SELECT u.user_id, us.orig_posts_count, us.sugg_posts_count, IF(us.user_id='4', 'posterview', COUNT(case when ISNULL(pv.post_id) AND p.private='1' AND p.user_id='2' AND p.sugg_by='2' then null else 1 end) ) as display_orig_posts_count, IF(us.user_id='4', 'posterview', COUNT(case when ISNULL(pv.post_id) AND p.private='1' AND p.user_id='2' AND p.sugg_by&lt;&gt;'2' then null else 1 end) ) as display_sugg_posts_count FROM users u JOIN user_stats us ON u.user_id=us.user_id JOIN posts p ON p.user_id=us.user_id LEFT JOIN private pv on pv.post_id = p.post_id AND pv.authorized_user_id='4' WHERE u.user_id='2' LIMIT 1 </code></pre> <p>The output should be:</p> <pre><code>user_id orig_posts_count sugg_posts_count display_orig_posts_count display_sugg_posts_count 2 3 2 1 2 </code></pre> <p>It however <a href="http://sqlfiddle.com/#!2/7c76e/6" rel="nofollow">outputs</a> as:</p> <pre><code>user_id orig_posts_count sugg_posts_count display_orig_posts_count display_sugg_posts_count 2 3 2 3 5 </code></pre> <p>I believe the reason for this is in the <code>JOIN posts p ON p.user_id=us.user_id</code></p> <p>If I turn it into <code>JOIN posts p ON p.user_id=us.user_id AND p.sugg_by='2'</code> (which matches the first COUNT for display_orig_posts_count), then the display_orig_posts_count is 1 which is correct, but display_sugg_posts_count is incorrect at 3. I get the correct display_orig_posts_count and incorrect display_sugg_posts_count. <a href="http://sqlfiddle.com/#!2/7c76e/5" rel="nofollow">Outputs</a> as:</p> <pre><code>user_id orig_posts_count sugg_posts_count display_orig_posts_count display_sugg_posts_count 2 3 2 1 3 </code></pre> <p>If I turn it into <code>JOIN posts p ON p.user_id=us.user_id AND p.sugg_by&lt;&gt;'2'</code> (which matches the second COUNT for display_sugg_posts_count), then the display_orig_posts_count is 2 which is incorrect, but display_sugg_posts_count is correct at 2. <a href="http://sqlfiddle.com/#!2/7c76e/4" rel="nofollow">Outputs</a> as:</p> <pre><code>user_id orig_posts_count sugg_posts_count display_orig_posts_count display_sugg_posts_count 2 3 2 2 2 </code></pre> <p>Basically, a logged in user if steph should return 'posterview', but any other user that uses COUNT should only see posts that are public (private 0), or (private 1) only if they are a part of it, and then count based on the condition in the COUNT clause to get the correct output as stated. I've been stuck on this for hours. Any idea how to get the query to work correctly? Note: Fiddles are included for each example output.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USMaverick
    1. USMaverick
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload