query 3 tables to retrieve fields value and display table in a page

[danielbala]

hi..

 

Im new to mysql..i want to know how to generate daily reports for all employees

 

i created 3 tables with fields

 

employee table:emp_id,name

job table:job_id,job_name

activity table:act_id,emp_name,job_name,date,activity done(assigned values as email,phonecall,visits)

 

Daily Activity Report : (Employee ID, Emp Name,Jobs Name, Activity Done)

          Eg:  1.| Date | Rajeev  |  Income Tax Filing    | Phone Call

  Options to Select : 1. Which Date (Required - Not Null) 2. Employee Name,

if Employee Name not selected, then the list of Activities for that day for all Employees.

 

How to write query

[sunfighter]

Everything you want to show is in the last table. You don't need the first two. But I think you should reconsider how you have things set up.

First thing I see is the three activities under activity table.activity done How are you going to handle an email AND a visit? And how about several emails? Maybe change the to three columns and have how many times as the value.

 

You have no way of knowing who has no job assignment nor what jobs are not active. You could have 5 workers and 2 of them straddled with most of the work. You should link the employee table and job table together. And the  job table to activity table and drop some of the column there.

 

Try googleing Database Normalization or something similar.