Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2011
    Posts
    3

    Question Unanswered: How to make 3table Query

    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.


    Thanks in advance to get ride on it.
    Last edited by razbin; 06-26-11 at 19:21.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    why did AA and AC do the same job more than once? and why aren't you showing the combined score in your results?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2011
    Posts
    3

    how to make 3table Query

    Quote Originally Posted by r937 View Post
    why did AA and AC do the same job more than once? and why aren't you showing the combined score in your results?
    Hi r937
    Thanks for ur reply. it just an example. here 1 is not a job name its a quantity of job given whether it failed or done. my software using this pattern so i cant change table format.i just want to generate report like i want.

  4. #4
    Join Date
    May 2004
    Posts
    144
    hi
    i usually follow this procedure for this kind of things:

    first create a view out of job_done, that contains emp_id and number of jobs that have done:

    Code:
    CREATE VIEW vwNumOfJobs
    SELECT COUNT(*) as  num_job_done, emp_id
    GROUP BY emp_id
    and u should create another view like that for failed table.

    now. you can join table "employee" with that two view.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •