By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,952 Members | 1,384 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,952 IT Pros & Developers. It's quick & easy.

Counting from multiple tables with the same column

HaLo2FrEeEk
100+
P: 404
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:

Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT
  2.   *
  3.   FROM tips T
  4.     LEFT JOIN comments C ON T.user_id = C.user_id
  5.     LEFT JOIN votes V ON T.user_id = V.user_id
  6.   WHERE T.user_id = 1
My counts are wrong though. I tested with user_id 1 (me). I've posted 11 tips and 4 votes, but the count I get is 44, which is 11*4, so something's wrong.

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.

Anyone have any ideas?

Edit: 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.
Apr 13 '11 #1
Share this Question
Share on Google+
1 Reply


Rabbit
Expert Mod 10K+
P: 12,347
Because it's joined on user ID, it's going to get multiplied.
Expand|Select|Wrap|Line Numbers
  1. ID Vote | ID Tip
  2. 1  3    | 1  Something
  3. 1  2    | 1  Something Else
  4. 1  1    |
  5.  
  6. Results
  7. VID Vote TID Tip
  8. 1   3    1   Something
  9. 1   3    1   Something Else
  10. 1   2    1   Something
  11. 1   2    1   Something Else
  12. 1   1    1   Something
  13. 1   1    1   Something Else
What you want to do is a union query.
Apr 13 '11 #2

Post your reply

Sign in to post your reply or Sign up for a free account.