Nested select statements not working

Question

I have 2 tables I need to search.

1st table Name: Personnel Fields: id, lname, fname, department, extension

2nd table Name: department Fields: id, department

The 2nd table has the full department name, while the 1st table has the id associated with the department. When the user searches, it should search for the lname, fname, and extension from the 1st table, and department from the 2nd table. I have the following sql code to run the search.

$sql = $wpdb->get_results("SELECT * FROM " . $wpdb->prefix . 
"dir_personnel where lname like '$search%' or fname like '$search%' or 
extension like '$search%' or department=(SELECT id FROM " . $wpdb->prefix . 
"dir_departments where department like '$search%') order by lname");

However, the following code runs fine by themselves.

$sql = $wpdb->get_results("SELECT * FROM " . $wpdb->prefix . 
"dir_personnel where lname like '$search%' or fname like '$search%' or 
extension like '$search%' order by lname");

and

$sql = $wpdb->get_results("SELECT id FROM " . $wpdb->prefix . 
"dir_departments where department like '$search%'");

I'm sure it's something simple I'm missing, but any help is appreciated.

Answer 1

You need to identify the tables for each of the columns that have the same name. You should also be using an IN statement and not = for the department.

I can't test this since I don't have your DB but you can try this:

$sql = $wpdb->get_results("SELECT * FROM " . $wpdb->prefix . 
"dir_personnel as personnel where lname like '$search%' or fname like '$search%' or 
extension like '$search%' or personnel.department IN (SELECT id FROM " . $wpdb->prefix . 
"dir_departments as departments where departments.department like '$search%') order by lname");