MySQL query help

I need help to write a query to count the total of a user in 2 tables. Count total based on userID in the following fields by month pos_start_userID, pos_end_userID, transfer_1_userID, transfer_2_userID, transfer_3_userID.

For example: The total pos count for John Doe in the month of September is 3, for October is 1, and for November is 1.

TABLE users
user_id
user_name

users VALUES
(1,'John Doe),
(2,'Jane Smith),
(3,'Kevin Brown');

TABLE pos
pos_id
pos_date
pos_start_amount
pos_end_amount
pos_start_userID
pos_end_userID

pos VALUES
(1,'2012-09-05 00:00:00',100,320,1,1),
(2,'2012-09-15 00:00:00',230,543,1,3),
(3,'2012-10-14 00:00:00',40,56,2,3),
(4,'2012-11-03 00:00:00',24,124,2,1),
(5,'2012-11-25 00:00:00',20,75,3,3);

TABLE pos_transfer
pos_id
transfer_1
transfer_2
transfer_3
transfer_1_userID
transfer_2_userID
transfer_3_userID

pos_transfer VALUES
(1,20,'','',2,'',''),
(2,45,70,'',1,3,''),
(3,50,120,20,2,2,1),
(4,43,'','',3,'',''),
(5,24,30,'',2,3,'');

Thank you

Are you just trying to count the amount of times a user is in 2 tables? If so you can just use a query = "SELECT statement with a JOIN and WHERE criteria of user_id = 'user_id'

and then: $num_users = mysql_num_rows ($queryResult);

$num_users = number of user in both tables?

Yes that's exactly what I want to do. Can you write a sample query for me to see.

Thank You

What language are you writing the Query in?

Hi,

do not use mysql_num_rows() to count rows. That's the most unclean and slow way to do this. Counting rows is what MySQL is for:

BitZoid Im using PHP.

@ Jacques1: Thanks for the concern but so far BitZoid is the only one that have a solution for me. I'm learning more and more each day and I will keep this tip in mind.

No clean solutions for you, but I can guide you

$queryUsers = "SELECT user FROM table1, table 2 WHERE user='criteria'";

$resultUsers = mysql_query($queryUsers);

$user_count = mysql_num_rows ($resultUsers);


Note** This uses EquiJoin which is being depricated, instead of the newer more relevant Inner Join.

Thanks BitZoid I will give this a try.

this is grossly inefficient

use COUNT(*) in sql to count rows

I already said that. But since nobody else posted a solution, they sticked with it.



@ caplocks:

I don't understand the "pos" logic. What do "pos" and "pos_transfer" with their columns mean? And how exactly do you count the users?

In any case, you'll need to JOIN the tables (like BitZoid said), GROUP them by the user ID, the MONTH() and the YEAR() and then select the COUNT(*) to calculate the number of rows for each user, month and year.

oh yeah, I'm in SQL Database class now in school and I am still using my old outdated methods.

SELECT user_id, COUNT(user) AS [User Count] FROM table 1 INNER JOIN table 2 ON table1.user=table2.user GROUP BY user_id

Does this look right?

not in mysql, no

those square brackets are a microsoftism and will work only in sql server and access