join question

[speedy33417]

I'm working on an employee schedule display. I suck at join statements, I just can't get my head around it. I've been basically avoiding them.
Now it seems I can't any longer.
I have a table where I store the jobs.

[code]====
jobs
====
job_id
customer_id
address_id
job_date[/code]

In an other table I have the assigned employees. It's not always just one employee. It could be any number of them. That's the reason it's a separate table.

[code]=============
jobs_employee
=============
assign_id
job_id
employee_id[/code]

My problem is that the employee (or employees) assigned to the jobs are outside of the jobs table completely.

Each employee can log in and check their own schedule and if they're assigned to a location with somebody else they have that information as well. Also, normally an employee would have 4-6 jobs for one day. job_date is a datetime in a 0000-00-00 00:00:00 format with the specific appointment date and time.

I need to join select job_id, customer_id, address_id and job_date where employee_id = $userId and also where job_date is between $jobLowDate and $jobHighDate and order it by job_date.

Thanks for your help.

[btherl]

One way to think about joins is that you are looking for matching rows.  In this case, you want to match job_id in jobs with job_id in jobs_employee.

The basic statement would be

[code=php:0]SELECT * FROM jobs JOIN jobs_employee ON (jobs.job_id = jobs_employee.job_id)[/code]

And the result of this will be a list of joined rows, where the employee's data is repeated for each job they work on.

Then to restrict it to particular dates, just add a WHERE clauses, such as

[code=php:0]WHERE job_date >= '$jobLowDate' AND job_date <= '$jobHighDate'[/code]

Then order the results:

[code=php:0]ORDER BY job_date[/code]