CODEWITHSUNDEEP
phpmysql
DobotJr
DobotJr

Reputation: 4049

Searching in 2 tables in one query MYSQL

I'm currently learning mysql, so I am very new to this, tried to find a solution elsewhere but was unable too.

I've got 2 tables, import_staff and import_student.

I've got a form the allows the user to search by name for either a staff user or a student user.

I can get my query to search through one table like this:

SELECT * FROM import_staff WHERE CONCAT(stafffirstname,space(1),stafflastname) LIKE '%$queryString%' LIMIT 10

BUT..I want the query to search through in both tables.. Not sure how to even start. Any ideas are appreciated. Thanks.

BTW-Using PHP.

Upvotes: 0

Views: 103

Answers (4)

kasavbere
kasavbere

Reputation: 6003

select * from (
    select id, stafffirstname as firstname, stafflastname as lastname  from import_staff
    union
    select id, studentfirstname as firstname, studentlastname as lastname  from import_student) t
where concat_ws(' ',firstname,lastname) like '%term%'

In your table definition, instead of studentlastname and the like you should just use lastname. The table names, import_staff and import_student, already provide namespace.

Upvotes: 0

fuzzyLikeSheep
fuzzyLikeSheep

Reputation: 473

SELECT  t1.stafffirstname, t1.stafflastname, t2.studentfirstname, t2.studentlastname,
FROM import_staff t1, import_student t2
WHERE CONCAT(t1.stafffirstname,space(1),t2.stafflastname) LIKE '%$queryString%' LIMIT 10

This might work for you.

Upvotes: 0

cetver
cetver

Reputation: 11829

Also you can do it with join

SELECT *
FROM import_staff
INNER JOIN import_student
ON 1 = 1

Upvotes: 0

aleroot
aleroot

Reputation: 72636

You can do it with a UNION :

SELECT id, stafffirstname AS name1, stafflastname AS name2 FROM import_staff WHERE CONCAT(stafffirstname,space(1),stafflastname) LIKE '%$queryString%' LIMIT 10
UNION ALL
SELECT id, studentfirstname AS name1, studentlastname AS name2 FROM import_student WHERE CONCAT(studentfirstname,space(1),studentlastname) LIKE '%$queryString%' LIMIT 10

Upvotes: 1

Related Questions