Page 1 of 1

join question

Posted: Sun Jan 14, 2007 11:21 pm
by 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: Select all

====
jobs
====
job_id
customer_id
address_id
job_date
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: Select all

=============
jobs_employee
=============
assign_id
job_id
employee_id
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.

Posted: Sun Jan 14, 2007 11:35 pm
by feyd
It appears you have the basic idea down..

Code: Select all

select
  jobs.job_id, jobs.customer_id, jobs.address_id, jobs.job_date
from jobs
inner join jobs_employee
  on
  (
    jobs.job_id = jobs_employee.job_id
    and
    jobs_employee.employee_id = $userid
  )
where
  jobs.job_date between '$jobLowDate' and '$jobHighDate'

Posted: Mon Jan 15, 2007 7:42 am
by speedy33417
That's great feyd! I need to do a couple of these before it comes natural. To add the ordering by date is it ORDER BY job_date or ORDER BY jobs.job_date?

I think my problem is that I haven't found a good tut on joins. It's very easy to get lost in this whole thing. Can someone recommend me a good one?

Posted: Mon Jan 15, 2007 9:29 am
by feyd
I can't recommend any tutorials about joins. In order to learn them, I just screwed around with them enough that it made sense. Took a few hours for myself.