how to make this query with 3 table

[rezbin]

Hi,
can anyone show me the procedure to make this happen, i am new on this.
emp_id--Name
1-------AA
2-------AB
3-------AC
4-------AD
5-------AE
6-------AF
7-------AZ

2)table job_done
emp_id --- date - --job_done --score
1 - - - 22-06-2011 -------1-------50
3 - - - 23-06-2011--------1-------25
5 - - - 24-06-2011--------1-------70
1 - - - 25-06-2011--------1-------50
3 - - - 26-06-2011--------1-------25

3)table job_failed
emp_id - -- date -- job_failed
2 - - - 22-06-2011 -------1
4 - - - 24-06-2011 -------1
6 - - - 26-06-2011 -------1
5 - - - 21-06-2011 -------1

Now how can i get result like
Name - -job_done - job_failed -score - job_handled - success_rate
AA---------2----------0----------50-------- 2 -----------100%
AB---------0----------1----------0 -------- 1 ----------- 0%
AC---------2----------0----------25-------- 2 -----------100%
AD---------0----------1----------0 -------- 1 ----------- 0%
AE---------1----------1----------70-------- 2 ----------- 50%
AF---------0----------1----------0 -------- 1 ----------- 0%

1)here date will not visible but i must able to search by date from both job_done and failed date by one selected range but i'm only can manage with one date either table 2 or 3 .
2)if a employee like 'AZ' not give any job will exclude from report.

 

[SimonSellick]

You need to filter the two results tables by date and summarise them by employee:

SELECT
  emp_id
, sum(job_done) num_jobs
, avg(score) avg_score
FROM job_done
WHERE date BETWEEN (@from AND @to)
GROUP BY
  emp_id

and

SELECT
  emp_id
, sum(job_failed) num_jobs
FROM job_failed
WHERE date BETWEEN (@from AND @to)
GROUP BY
  emp_id

Then you need to join the summarised tables to the employee table:

SELECT
  e.name
, jd.num_jobs job_done
, jf.num_jobs job_failed
, jd.avg_score score
, IFNULL(jd.num_jobs, 0) + IFNULL(jf.num_jobs, 0) job_handled
, (100 * IFNULL(jd.num_jobs, 0)) / IFNULL(jd.num_jobs, 0) + IFNULL(jf.num_jobs, 0) success_rate
FROM employees e
  LEFT JOIN (
    SELECT
      emp_id
    , sum(job_done) num_jobs
    , avg(score) avg_score
    FROM job_done
    WHERE date BETWEEN (@from AND @to)
    GROUP BY
      emp_id
  ) jd ON jd.emp_id = e.emp_id
  LEFT JOIN (
    SELECT
      emp_id
    , sum(job_failed) num_jobs
    FROM job_failed
    WHERE date BETWEEN (@from AND @to)
    GROUP BY
      emp_id
  ) jf on jf.emp_id = e.emp_id
WHERE jd.num_jobs IS NOT NULL
  OR jf.num_jobs IS NOT NULL
ORDER BY
  name

I have added parameters (@from, @to) to filter the dates but you could specify these in other ways. This is not tested because I have no copy of MySQL handy.
HTH

 

[rezbin]

dear SimonSellick,

Thanks a lot for your help.however its showing error when i tried to execute. will you please check again if we missed something?

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY
emp_id
) jd ON jd.emp_id = e.emp_id
LEFT JOIN (
SELEC' at line 16

 

[towerbase]

If you are new to SQL then I suggest you read Simply MySQL by Rudi Limeback. It is a great introduction to SQL. Check out the reviews on Amazon if you don't believe me.

Tek-tips is not a free programming service. So the solution I'm giving you provides only part of the answer - it copes with the hard part (working out the Success Rate) and you should study it and work out how to give the query. If you get stuck then come back and show us what you have tried.

Your request can be done with one SQL statement in MySQL along the lines of:

SELECT emp.name AS `Name`
     , 0 AS `Job Done`    -- Replace the 0 with your own code
     , 0 AS `Job Failed`  -- Replace the 0 with your own code
     , 0 AS `Score`       -- Replace the 0 with your own code
     , SUM( IFNULL( jd.job_done, 0 ) ) + SUM( IFNULL( jf.job_failed, 0 ) ) as `Job Handled`
     , CASE WHEN SUM( IFNULL( jd.job_done, 0 ) )=0 THEN '0%' 
	         WHEN SUM( IFNULL( jf.job_failed, 0 ) )=0 THEN '100%'
	         ELSE CONCAT( SUM( IFNULL( jd.job_done, 0 ) ) * 100 / ( SUM( IFNULL( jd.job_done, 0 ) ) + SUM( IFNULL( jf.job_failed, 0 ) ) ), '%' ) 
		 END AS `Success Rate`
	
FROM emp
LEFT JOIN job_done AS jd on emp.emp_id=jd.emp_id
LEFT JOIN job_failed AS jf on emp.emp_id=jf.emp_id
GROUP BY emp.name
HAVING `Job Done` + `Job Failed` > 0

Andrew

 

[rezbin]

Hello towerbase,
Thanks for your advice. sure i will go for this book. and the good news is i solved the problem.

i have a question, dont know is it logical or not,

is it possible to join 3 table and UNION ALL only a single column from both table like date column in a single query?

 

[towerbase]

Well done for solving your original problem. You will have learnt a lot more by doing it yourself than blindly copying someone else's solution.

I don't understand your question:

is it possible to join 3 table and UNION ALL only a single column from both table like date column in a single query?

If you have a new question then you should start a new thread. However, you should try to solve it yourself before posting it to Tek-Tips.

Here are some guidelines on how to get the best out of Tek-Tips:
faq20-2883

Andrew

 

[rezbin]

Hello towerbase,
i think my question is related with this topic so i am stating again here.
my question is, can i use "UNION ALL" and "JOIN" together?

here is my query:
SELECT e.name, jd.num_jobs job_done, jf.num_jobs job_failed, jd.avg_score score, IFNULL(jd.num_jobs, 0) + IFNULL(jf.num_jobs, 0) job_handled,
(100 * IFNULL(jd.num_jobs, 0)) / IFNULL(jd.num_jobs, 0) + IFNULL(jf.num_jobs, 0) success_rate
FROM employees e
LEFT JOIN (SELECT emp_id , sum(job_done) num_jobs, avg(score) avg_score FROM job_done GROUP BY emp_id) jd ON jd.emp_id = e.emp_id
LEFT JOIN (SELECT emp_id, sum(job_failed) num_jobs FROM job_failed GROUP BY emp_id) jf on jf.emp_id = e.emp_id
WHERE jd.num_jobs IS NOT NULL OR jf.num_jobs IS NOT NULL
ORDER BY name

and i want to put a another column name DATE with combination of table 2 date and table 3 date.
is it possible?
thanks for keep maintain your help