CODEWITHSUNDEEP
phpmysql
youaremysunshine
youaremysunshine

Reputation: 351

display multiple data from multiple different table

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

Upvotes: 2

Views: 150

Answers (5)

Ikong
Ikong

Reputation: 2570

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}'

Upvotes: 0

jpruizs
jpruizs

Reputation: 485

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 = ?

Upvotes: 0

Bora
Bora

Reputation: 10717

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

Upvotes: 0

Code Lღver
Code Lღver

Reputation: 15593

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.

Upvotes: 1

Jay
Jay

Reputation: 841

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 ;

Upvotes: 0

Related Questions