Count query

Hi All
Can someone help me with creating a query please, I have three tables, User,job1,job2,job3 what I want is to know the count of each job a user has done. User table has primary key (id) all job tables have foreign key from User table (user_id). I just can not understand how to do it?
This is what I have so far but it doesn't give the result I want

$query = "SELECT job1.job_id,job2.job_id,job3.job_id,
COUNT(*)
FROM job1,job2,job3,user
WHERE job1.user_id = user.id OR
job2.user_id = user.id OR
job3.user_id = user.id 
GROUP BY job_id";
$result = mysql_query($query) or die(mysql_error());
		$num_rows = mysql_num_rows($result);
echo "Normal: &nbsp; $num_rows<p>";

Code (markup):

Many thanks well in advance
Zed

 

If I understand correctly, this sql *should* work:

Select job1.job_id, job2.job_id, job3.job_id, 
Count(*) as NumRecords
From job1
inner join job2 on job2.user_id = user.id
inner join job3 on job3.user_id = user.id
group by user_id

Code (markup):

that's if the records Span all tables, otherwise (which i suspect) this would be what you need

Select job1.job_id, job2.job_id, job3.job_id, 
Count(*) as NumRecords
From job1
right join job2 on job2.user_id = user.id
right join job3 on job3.user_id = user.id
group by user_id

Code (markup):

 

Thanks firemarsh for your reply but I'm getting this error

Not unique table/alias: 'blockBook' 

Code (markup):

Thanks
Zed

 

is 'blockBook' a field in your table structure?

 

if these tables contain non-unique fields, 2 questions come to mind...
1) Why have a job1, job2, job3 table if all the fields are the same?
if you're tracking 3 different job types, I would collapse to a single table, with a "unique identifier" for each job type.. Such As:

job_ID
user_id
job_typ   (for your example, use something like '1' or '2' or '3')
'blockBook' (whatever that is)
'job_comp' (job complete, boolean)

Code (markup):

This way, your query would be much easier to execute.

So in this example, (using job_comp), your query would become:

SELECT count(user_id) as total_by_user, job.user_id as User 
FROM job
 inner join user on job.user_id = user.user_id
where job.job_comp='True'
GROUP BY user.userID

Code (markup):

Then, you would return a list of all user IDs and the total completed by user, and could further break it down by job type (assuming thats why you have 3 tables) by simply changing it to

SELECT count(user_id) as total_by_user, job.user_id as User 
FROM job
 inner join user on job.user_id = user.user_id
where job.job_comp='True' and job_typ='1'
GROUP BY user.userID

Code (markup):

 

Thanks Firemarsh that was greate help... Cheers

Zed