I struggle to use join on multiple tables. When I try to do this:
SELECT `absences`.*, `employee`.*, `type`.*
FROM `absences`, `type`
LEFT JOIN `login`.`employee` ON `absences`.`employee_FK` = `employee`.`employee_ID`
I get this:
Unknown column 'absences.employee_FK' in 'on clause'
'absences.employee_FK' exists in my DB.
I want to display the user data and the type of the absence. How can I do that? I dont understand joins too well yet.
Looks like your just trying to join two tables, because you don't have a join condition for the type table in your query:
SELECT *
FROM absences
LEFT JOIN employee ON absences.employee_FK = employee.employee_ID
If you want to join to the type table too:
SELECT *
FROM absences
LEFT JOIN type ON absences.type_FK = type.type_ID
LEFT JOIN employee ON absences.employee_FK = employee.employee_ID
You have to select all the tables for using the JOIN condition.
The example goes like this:
SELECT `employee.*`, `absences.*`, `type.*`
FROM `employee`
JOIN `absences`
ON `employee`.`employee_ID` = `absences`.`employee_FK`
JOIN `type`
ON `absences`.`type_FK` = `type`.`type_ID`
JOIN `on_off`
ON `on_off`.`on_off_ID` = `employee`.`on_off_FK`;
You can modify the query as per your requirement.
You can work on the script below. Add Where clause at the end if necessary. Not tested...
SELECT * from absences a
inner join type t on (t.typeID = a.type_FK)
inner join employee e on (e.employee_ID = a.employee_FK)
This might be what you are looking for
select * from `absences` a
left outer join `employee` e on e.`employee_ID` on a.`employee_FK`
left outer join `type` t on t.`type_ID`=a.`type_FK`
left outer join `on_off` o on o.`on_off_ID`=e.`on_off_FK`
You have to use join for all tables:
SELECT `absences`.*, `employee`.*, `type`.*
FROM `absences`
JOIN `type` on `absences`.`type_fk` = `type`.`type_ID`
LEFT JOIN `login`.`employee` ON `absences`.`employee_FK` = `employee`.`employee_ID`
left joinshow be a table name. So, islogin.employeea table name? That meansloginis a database name andemployeeis a table inlogin. What's the output ofshow create table absencesis like? Maybe you misspelled the name of the column.