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.

[mikosiko]

transform your tables job_done and job_failed in only one table for better design,,, after that your queries will be easier

[rezbin]

Hi mikosiko,

this table layout was created and used by a software so i cant do that.i know this would be better. please help to get the 2nd option.

[mikosiko]

well... a couple LEFT JOIN and using COUNT() (or SUM) will give you the results... with exception of the "score" column because it will lose meaning at the aggregation time... work on some code and came back if you still have doubts.

[rezbin]

Hello Mikosiko,

Here is my Query's received from one great. and only resolved half of problem

 

select employee.name, ifnull(done,0) done,ifnull(failed,0) failed,ifnull(score,0) score

from employee

left join (select emp_id,sum(job_done) done,avg(score) score from job_done group by emp_id) done using(emp_id)

left join (select emp_id, sum(job_failed) failed from job_failed group by emp_id) failed using(emp_id)

order by employee.name;

 

Here im unable to add sum of done+failed = total, done/total*100= xx%. and finally combine both date in a single column so that i can search by date.

 

can you please give some code to finish it?

Thanks 

[mikosiko]

here is your fish:  .... replace any wrong column name with your own, and with the proper dates that you want (or variables)

 

select a.name,
       COUNT(b.job_done) AS job_done,
       COUNT(c.job_failed) AS job_failed,
       COUNT(b.job_done) + COUNT(c.job_failed) AS job_handled,
       (COUNT(b.job_done) * 100) / (COUNT(b.job_done) + COUNT(c.job_failed)) AS success_rate
FROM employee AS a
LEFT JOIN job_done AS b ON b.emp_id = a.emp_id AND b.fecha between '2011-06-21' AND '2011-06-26'
LEFT JOIN job_failed AS c ON c.emp_id = a.emp_id  AND c.fecha between '2011-06-21' AND '2011-06-26'
GROUP BY a.name
HAVING job_handled > 0;

 

anything else is for you to work on.

[rezbin]

Hello Mikosilko,

Thank u for you quick response. having problem with ur code. error showing "Unknown column 'b.fetcha' in 'on clause'"

[rezbin]

Dear mikosiko,

Thank you very much. it works now. really appreciating help.