Is LEFT JOIN what I'm looking for?

[LastGoodAirWave]

So I'm  a noob with mysql joins and I'm thinking I'm heading in the wrong direction.

I am trying to output a calendar with each shift listing which employee is working it.

I can easily output results 002 003 and 005 but obviously want output of Sam Mary and Jane.

I could put the employees DB into a PHP array and use that but I'm trying to learn MySQL better, plus this will always be growing.

Sometimes you have to dive in to learn. Is my thinking totally wrong on this one?

 

Even a simple answer of "don't use LEFT JOIN, use AS" or whatever will put me in the right direction would be appreciated.

I'm not asking you to solve my problem necessarily, I just don't know what to even search for.

Any help is appreciated. Thanks so much!

 

 

MySQL 5.1

   

Employees

+------+---------+

| id  | name    |

+------+---------+

|  001 | Joe    |

|  002 | Sam    |

|  003 | Mary    |

|  004 | Bill    |

|  005 | Jane    |

+------+---------+

 

 

Schedule

+-------+---------------+-----------+---------------+------------+

| id    | morning_shift | day_shift | evening_shift |  date    |

+-------+---------------+ ----------+---------------+------------+

|    1  |      002      |    003  |      005      | 2011-07-28 |

|    2  |      002      |    001  |      004      | 2011-07-29 |

+-------+---------------+-----------+---------------+------------+

 

This works fine:

SELECT name FROM employees LEFT JOIN schedule ON employees.id = schedule.morning_shift WHERE schedule.date = '2011-07-28'

   

 

How can I do something like this

SELECT name FROM employees 
LEFT JOIN employees ON schedule.morning_shift = employees.id 
LEFT JOIN employees ON schedule.day_shift = employees.id 
LEFT JOIN employees ON schedule.evening_shift = employees.id 
WHERE date = '2011-07-28'

error = Not unique table/alias: 'employees'

[ajlisowski]

I am going to think about your problem some more to see if there is a better way...

 

but

 

SELECT em.name AS morning_shift, ed.name AS day_shift, ee.name AS evening_shift FROM schedule AS s
LEFT JOIN employees AS em ON s.morning_shift = em.id 
LEFT JOIN employees AS ed ON s.day_shift = ed.id 
LEFT JOIN employees AS ee ON s.evening_shift = ee.id 
WHERE s.date = '2011-07-28'

 

should work.

 

Basically you want to think of each of your shifts as a foriegn key to another table (the employee). And you have to do a join to get the information from the row that key points to. So you need 3 joins, 1 for each of your keys. You were close, except you were trying to join employee on employee when you should be joining schedule to employee. And you need to give alias's to the joined tables so mysql knows which columns to pull from which table.

[mikosiko]

I will suggest you to redesign your schedule table removing the employee id from it and creating a 3rd table with the employees-by-schedule relation

 

schedule

Id

date

 

emp-schedule

id              (FK to the schedule table)

emp-id      (FK to the employees table)

shift-type  (morning,day,evening)

 

this should give you more flexibility (more than 1 employee per shift-type in one schedule per example) and the queries should be easier

 

[LastGoodAirWave]

Thank you ajlisowski! 

That works great!

 

I am looking in my O'Reilly "PHP and MySQL" book to try to understand this better. I thought maybe I needed an AS in there somewhere. The book is calling this an attribute alias and says is "useful for complex queries that need to use the same table twice but in different ways." It gives examples but I'll admit I've skipped ahead a little 

 

I'm not sure I completely grasp this yet but I now know that it can be done and can dive deeper.

I knew that I could use php to store the employees table in an array and then just use the output of sql to display the array, which would give me the same end result I'm looking for, but I really wanted to use MySQL on it's own. I'm assuming it's more efficient using MySQL anyways.

 

Well thank you again!

If you think of something else I'd love to hear it.

 

Cheers

[LastGoodAirWave]

Hi mikosiko.

 

After some frustration I started thinking more about my table structures and that they could be a poor design.

Do you mean something like this? Do I need to change my tables to InnoDB then? I just enabled it on my localhost, but not sure about my server.

 

 

Employees

+------+---------+

| id  | name    |

+------+---------+

|  001 | Joe    |

|  002 | Sam    |

|  003 | Mary    |

|  004 | Bill    |

|  005 | Jane    |

+------+---------+

 

Schedule

+-------+------------+

| id    |  date    |

+-------+------------+

|    1  | 2011-07-28 |

|    2  | 2011-07-29 |

+-------+------------+

 

Employee_Schedule

+-------+---------------+----------+

| id    |  shift_type  | employee |

+-------+---------------+----------+

|    1  |      morn    |    003  |

|    2  |      day      |    001  |

|    3  |      eve      |    005  |

+-------+---------------+----------+

 

I'm not sure how the relationship between the dates and the employee_schedule would work. How will I know which date is related to the employee_schedule?

 

Also, wouldn't I need to use a double insert then like this?

 

   INSERT INTO schedule (id, date) 
     VALUES('3', '2011-07-30');

   INSERT INTO employee_schedule (id, shift_type, employee) 
     VALUES('4', 'day', '005');

 

[mikosiko]

your schedule table will be ok in that way... your Employee_schedule table should be something like this: (using the data from your first post as an example)

 

ID      employee      type

1        2                        morn       

1        3                        day

1        5                        eve

2        2                        morn

2        1                        day

2        4                      eve

 

for this table as you see the ID correspond to the schedule ID. hence the relation is obvious.

 

Regarding to InnoDB... you should use it if you want to implement and enforce Referencial Integrity constraints (Foreing key p.e).

and regarding to the inserts... yes you must use INSERTs for each table involved ... hope this help

[LastGoodAirWave]

Okay I got it now. I'm a little slow I guess. I always think of id's as unique.

 

Thanks for your help guys.  Posting this as "Solved"

 

Cheers