join question

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
speedy33417
Forum Contributor
Posts: 128
Joined: Sun Jul 23, 2006 1:14 pm

join question

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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'
User avatar
speedy33417
Forum Contributor
Posts: 128
Joined: Sun Jul 23, 2006 1:14 pm

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
Post Reply