query - Nested select statements not working

admin管理员组

文章数量:1130349

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.

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.

• query
• sql
• select

Share Improve this question asked Nov 20, 2018 at 19:55 Darth Mikey DDarth Mikey D 10511 silver badge99 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

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");

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.

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.

• query
• sql
• select

Share Improve this question asked Nov 20, 2018 at 19:55 Darth Mikey DDarth Mikey D 10511 silver badge99 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

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");

本文标签： queryNested select statements not working