title: Counting results from multiple tables with same column
body: <p>I have a system where, essentially, users are able to put in 3 different pieces of information: a tip, a comment, and a vote.  These pieces of information are saved to 3 different tables.  The linking column of each table is the user ID.  I want to do a query to determine if the user has any pieces of information at all, of any of the three types.  I'm trying to do it in a single query, but it's coming out totally wrong.  Here's what I'm working with now:</p>
  
  <pre><code>SELECT DISTINCT
    *
    FROM tips T
    LEFT JOIN comments C ON T.user_id = C.user_id
    LEFT JOIN votes V ON T.user_id = V.user_id
    WHERE T.user_id = 1
  </code></pre>
  
  <p>This seems to only be getting the tips, duplicated for as many votes or comments there are, even if the votes or comments weren't made by the specified user_id.</p>
  
  <p>I only need a single number in return, not individual counts of each type.  I basically want a sum of the number of tips, comments, and votes saved under that user_id, but I don't want to do three queries.</p>
  
  <p>Anyone have any ideas?</p>
  
  <p><strong>Edit</strong>: Actually, I don't even technically need an actual count, I just need to know if there are any rows in any of those three tables with that user_id.</p>
  
  <p><strong>Edit 2:</strong> I almost have it with this:</p>
  
  <pre><code>SELECT
    COUNT(DISTINCT T.tip_id),
    COUNT(DISTINCT C.tip_id),
    COUNT(DISTINCT V.tip_id)
    FROM tips T
      LEFT JOIN comments C ON T.user_id = C.user_id
      LEFT JOIN votes V ON T.user_id = V.user_id
    WHERE T.user_id = 1
  </code></pre>
  
  <p>I'm testing with user_id 1 (me).  I've made 11 tips, voted 4 times, and made no comments.  My return is a row with 3 columns: 11, 0, 4.  That's the proper count.  However, I tested it with a user that hasn't made any tips or comments, but has voted 3 times, that returned 0 for all counts, it should have returned: 0, 0, 3.</p>
  
  <p>The problem that I'm having seems to be that if the table that I'm using for the WHERE clause doesn't have any rows from that user_id, then I get 0 across the board, even if the other tables DO have rows with that user_id.  I could use this query:</p>
  
  <pre><code>SELECT
    (SELECT COUNT(*) FROM tips WHERE user_id = 2) +
    (SELECT COUNT(*) FROM comments WHERE user_id = 2) +
    (SELECT COUNT(*) FROM votes WHERE user_id = 2) AS total
  </code></pre>
  
  <p>But I really wanted to avoid running multiple queries, even if they're subqueries like this.</p>
  
  <p><strong>UPDATE</strong></p>
  
  <p>Thanks to ace, I figured this out:</p>
  
  <pre><code>SELECT
    (COUNT(DISTINCT T.tip_id) + COUNT(DISTINCT C.tip_id) + COUNT(DISTINCT V.tip_id)) AS total
    FROM users U
      LEFT JOIN tips T ON U.user_id = T.user_id
      LEFT JOIN votes V ON U.user_id = V.user_id
      LEFT JOIN comments C ON U.user_id = C.user_id
    WHERE U.user_id = 4
  </code></pre>
  
  <p>the users table contains the actual information bout the user including, obviously, the user id.  I used the user table as the parent, since I could be 100% sure that the user would be present in that table, even if they weren't in the other tables.  I got the proper count that I wanted with this query!</p>