[SOLVED] Count query

[zed420]

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:   $num_rows<p>";

Many thanks well in advance

Zed

 

[fenway]

I'm so very confused... your grouping by job_id, not user_id?

[zed420]

Don't worry fenway so was I... this is what I was looking for

 

SELECT id
     , ( SELECT COUNT(*)
           FROM job1
          WHERE user_id = user.id ) +
       ( SELECT COUNT(*)
           FROM job2
          WHERE user_id = user.id ) +
       ( SELECT COUNT(*)
           FROM job3
          WHERE user_id = user.id ) AS jobs_done
  FROM user

 

Thanks

Zed