PHP MySQL Select multiple tables

Question

Is it posibble to select 3 tables at a time in 1 database?

Table 1: employee
              --> employee_id
              --> first_name
              --> last_name
              --> middle_name
              --> birthdate
              --> address
              --> gender
              --> image
              --> salary

Table 2: logs
              --> log_id
              --> full_name 
              --> employee_id
              --> date
              --> time
              --> status

Table 2: logout
              --> log_id
              --> full_name 
              --> employee_id
              --> date
              --> time
              --> status

I wanted to get the value of employee table where $id of selected. Then the $id also get the value of log.time, log.date, logout.time, and logout.date.

I already try using UNION but nothing happens.

Answer 1

Something like:

SELECT * FROM `employee` as `e` ( LEFT JOIN `logs` ON `e`.`employee_id` = `logs`.`employee_id`) LEFT JOIN `logout` ON `e`.`employee_id` = `logout`.`employee_id` WHERE `e`.`employee_id` = $id

You want to join the tables together using their related column, the employee id. This sql statement first joins employee to logs, then that result is joined to logout.

Answer 2

Up to you to put it in your app, but the query looks like this. Dan's query will not return exactly what you said you're looking for.

SELECT e.*, l.time, l.date, lo.time, lo.date 
  FROM employee e
  LEFT JOIN logs l 
    ON l.employee_id = e.employee_id
  LEFT JOIN logout lo
    ON lo.employee_id = e.employee_id
  WHERE e.employee_id = {your id here}

Answer 3

Jordan,

To expand on the query Entendu gave (I can't reply to that), you can use aliases:

SELECT e.*, l.time AS l_time, l.date AS l_date, lo.time AS lo_time, lo.date AS lo_date

FROM employee e LEFT JOIN logs l ON l.employee_id = e.employee_id LEFT JOIN logout lo ON lo.employee_id = e.employee_id WHERE e.employee_id = {your id here}

This way you can call $row['l_time'] to get the value of l.time