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

5 Years
Discussion Span
Last Post by reco21

job_name in table activity should be job_id.

select * from employee e, job j, activity a 
where e.emp_id = a.emp_id and j.job_id = a.job_id and a.date = <selected_date>
/* optional: */ and e.name = <selected_name>

But you will never learn anything from the homework of others.

Edited by smantscheff: n/a

Votes + Comments
ty for your snippet

Thanks for this I came here looking for the same thing.. Thought I would have to do a bunch of joins but this worked perfect.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.