I have 3 different table in my database which are:
Leave {leave_id, leave_type, emp_id}
Employee {emp_id. emp_name, dept_id}
Department {dept_id, dept_name}
I wish to select leave_type, emp_name and dept_name based on leave_id.
How should I write this query?
Thanks
Select l.leave_type, e.emp_name, d.dept_name
FROM Leave l
JOIN Employee e ON l.emp_id = e.emp_id
JOIN Department d ON e.dept_id = l.dept_id
WHERE l.leave_id = '1'; //give leave_id which you want
Use the above query.
Try this
select leave_type, emp_name ,dept_name from Leave, Employee, Department where Leave.emp_id = Employee.emp_id AND Employee.dept_id = Department.dept_id ;
Try this one
SELECT leave_type, emp_name, dept_name
FROM Leave as l
JOIN Employee as e ON e.emp_id = l.leave_id
JOIN Department as d ON d.dept_id= l.leave_id
WHERE leave_id = $id
This option will return all rows where the join condition is met.
SELECT leave_type, emp_name, dept_name
FROM Leave
INNER JOIN Employee ON Leave.emp_id = Employee.emp_id
INNER JOIN Department ON Employee.dept_id = Department.dept_id
WHERE Leave.leave_id = ?
USE LEFT JOIN to Force join even when one of them has no value.
SELECT leave.leave_type, emp.emp_name, dept.dept_name
FROM Employee emp
LEFT JOIN Leave leave on emp.emp_id = leave.emp_id
LEFT JOIN Department dept on dept.dept_id = emp.dept_id
WHERE
leave.leave_id='{$YOUR_VALUE}'
MySQL Join. Also: codinghorror.com/blog/2007/10/… AND tutorialspoint.com/mysql/mysql-using-joins.htm