MySQL Question - Pulling From Two Tables And Merging

[JustinK101]

Hello, I have two tables which store the exact same fields with the exact same field names. One table is called employees and the other inactive_employees. I need to run a query and pull from employees and inactive_employees and merge the results.

Currently I have the following query:

$sql = "SELECT id, first_name, last_name, job_title_occupation, current_clock_status, last_succ_login FROM employees, inactive_employees ORDER BY last_name ASC";

The problem is that id, first_name, last_name, job_title_occupation, current_clock_status, and last_succ_login is ambigious since they are called the same thing in both tables. How do i pull the following fields from both tables and return one result. Thanks much.

[btherl]

That syntax will do a join.. I think you want a union.  Try:

[code](SELECT id, first_name, last_name, job_title_occupation, current_clock_status, last_succ_login FROM employees)
UNION ALL
(SELECT id, first_name, last_name, job_title_occupation, current_clock_status, last_succ_login FROM inactive_employees)
ORDER BY last_name ASC[/code]

That will merge the two tables.  "UNION ALL" will not remove any duplicates, which is usually faster.  "UNION" will remove any duplicates which are in both table.

If you want to do some kind of merging between duplicates in the two tables, then you need to do a join instead.. post again and we can help you with that :)