0
SELECT
  status,
  schedule_switch.Switch_Employee_ID,
  schedule_switch.Requesting_Employee_ID,
  employee_info.FirstName,
  DATE_FORMAT(schedule_switch.date, '%m-%d-%Y') AS switchdate,
  DATE_FORMAT(time_slot.date, '%m-%d-%Y') AS date,
  start_time,end_time
FROM
  work_schedule
    join time_slot on work_schedule.Slot_ID = time_slot.Slot_ID
    join manager_info on manager_info.Manager_ID = time_slot.Manager_ID
    join schedule_switch on work_schedule.Schedule_ID = schedule_switch.Schedule_ID
    join employee_info on schedule_switch.switch_Employee_ID = employee_info.Employee_ID
    join employee_info on schedule_switch.requesting_Employee_ID = employee_info.Employee_ID
where
  status is null
order by
  switchdate;

I am stuck with this query. I need to get the first name of switch_employee_id and requesting_employee_id both connected to one primary key Employee_ID in the employee_info table.

1
  • I don't know mysql but in mssql would use a table alias
    – paparazzo
    Apr 5 '16 at 17:14
2

Try this

SELECT
    status,
    schedule_switch.Switch_Employee_ID,
    schedule_switch.Requesting_Employee_ID,
    empl_info1.FirstName,
    empl_info2.FirstName, //added the modified join select column
    DATE_FORMAT(schedule_switch.date, '%m-%d-%Y') AS switchdate,
    DATE_FORMAT(time_slot.date, '%m-%d-%Y') AS date,
    start_time,
    end_time
FROM work_schedule
    JOIN time_slot ON work_schedule.Slot_ID = time_slot.Slot_ID
    JOIN manager_info ON manager_info.Manager_ID = time_slot.Manager_ID
    JOIN schedule_switch ON work_schedule.Schedule_ID = schedule_switch.Schedule_ID
    JOIN employee_info empl_info1 ON schedule_switch.switch_Employee_ID = empl_info1.Employee_ID
    // modified your join
    JOIN employee_info empl_info2 ON schedule_switch.requesting_Employee_ID = empl_info2.Employee_ID
WHERE status IS NULL
ORDER BY switchdate;
2
  • so it is just the ambiguity between names that was creating the error ?. thanks a lot man ... Apr 5 '16 at 19:17
  • Yes you joined the employee_info table twice with the same name. This causes the error by giving both joins an alias the error is solved Apr 5 '16 at 19:37

This site is temporarily in read only mode and not accepting new answers.

Not the answer you're looking for? Browse other questions tagged .