Single Query to return result from multiple tables in mysql

Question

I have the following tables

Table: tblperson

id	name
1	John
2	William
3	Adam

Table: tbloccupation

id	personid(fk)	occupation
1	1	Lawyer
2	1	Social worker
3	2	Doctor
4	2	Social worker
5	3	Teacher
6	3	Accountant

frontend application has single text box to search by name or occupation. i would like to create single select query to search in both tables.

ex: if someone search john, it should return one single row

|name | occupation |
|-----|--------------------|
|john |Lawer, social worker|

ex: if someone search for social worker, it should return two rows

| name | occupation |
|---------|-----------------------|
| john | Lawer, social worker |
| William | Doctor, Social worker |

Answer 1

PROPOSED QUERY

SELECT * FROM
(SELECT A.name,GROUP_CONCAT(B.occupation) occupations
FROM tbloccupation B INNER JOIN tblperson A
ON A.id = B.personid GROUP BY A.name) AA
WHERE occupations LIKE '%Social Worker%';

YOUR SAMPLE DATA

MySQL lalitmohan> DROP DATABASE IF EXISTS lalitmohan;
Query OK, 2 rows affected (0.03 sec)
MySQL (none)> CREATE DATABASE lalitmohan;
Query OK, 1 row affected (0.00 sec)
MySQL (none)> USE lalitmohan
Database changed
MySQL lalitmohan> CREATE TABLE tblperson
 -> (id int not null auto_increment,
 -> name varchar(20),
 -> primary key (id)
 -> );
Query OK, 0 rows affected (0.05 sec)
MySQL lalitmohan> INSERT INTO tblperson (name) VALUES ('John');
Query OK, 1 row affected (0.02 sec)
MySQL lalitmohan> INSERT INTO tblperson (name) VALUES ('William');
Query OK, 1 row affected (0.00 sec)
MySQL lalitmohan> INSERT INTO tblperson (name) VALUES ('Adam');
Query OK, 1 row affected (0.01 sec)
MySQL lalitmohan> CREATE TABLE tbloccupation
 -> (id int not null auto_increment,
 -> personid int not null,
 -> occupation varchar(20),
 -> primary key (id)
 -> );
Query OK, 0 rows affected (0.06 sec)
MySQL lalitmohan> INSERT INTO tbloccupation (personid,occupation) VALUES
 -> (1,'Lawyer'),
 -> (1,'Social Worker'),
 -> (2,'Doctor'),
 -> (2,'Social Worker'),
 -> (3,'Teacher'),
 -> (3,'Accountant');
Query OK, 6 rows affected (0.02 sec)
Records: 6 Duplicates: 0 Warnings: 0
MySQL lalitmohan> SELECT * FROM tblperson;
+----+---------+
| id | name |
+----+---------+
| 1 | John |
| 2 | William |
| 3 | Adam |
+----+---------+
3 rows in set (0.00 sec)
MySQL lalitmohan> SELECT * FROM tbloccupation;
+----+----------+---------------+
| id | personid | occupation |
+----+----------+---------------+
| 1 | 1 | Lawyer |
| 2 | 1 | Social Worker |
| 3 | 2 | Doctor |
| 4 | 2 | Social Worker |
| 5 | 3 | Teacher |
| 6 | 3 | Accountant |
+----+----------+---------------+
6 rows in set (0.00 sec)
MySQL lalitmohan>

PROPOSED QUERY TESTED

MySQL lalitmohan> SELECT * FROM
 -> (SELECT A.name,GROUP_CONCAT(B.occupation) occupations
 -> FROM tbloccupation B INNER JOIN tblperson A
 -> ON A.id = B.personid GROUP BY A.name) AA
 -> WHERE occupations LIKE '%Social Worker%';
+---------+----------------------+
| name | occupations |
+---------+----------------------+
| John | Lawyer,Social Worker |
| William | Social Worker,Doctor |
+---------+----------------------+
2 rows in set (0.00 sec)
MySQL lalitmohan>

Answer 2

A combination of join and group by and group_concat will go bed you your wanted result. Gropu_concat has also a order by so you can order as you want it

In your where clause you enter the wanted criteria If you collation is case sensitive you can always convert the search text to a ci collation see https://www.geeksforgeeks.org/how-to-search-case-insensitive-in-a-column-using-like-wildcard-in-mysql/

 SELECT p.name
 , GROUP_CONCAT(o.occupation) as occupation
FROM tblperson p JOIN tbloccopation o ON p.id = o.personid
wHERE p.name like 'John' OR o.occupation like 'social worker'
GROUP BY p.name

Answer 3

frontend application has single text box to search by name or occupation. i would like to create single select query to search in both tables.

This query can return the results as expected:

SELECT 
 pe.name, GROUP_CONCAT(oc.occupation SEPARATOR ', ') occupations 
FROM 
 tblperson pe, tbloccupation oc
WHERE 
 pe.id = oc.personid
 AND 
 pe.id IN ( 
 SELECT id FROM tblperson WHERE name = @input
 UNION
 SELECT personid FROM tbloccupation WHERE occupation LIKE @input
 )
GROUP BY 
 pe.name;

ex: if someone search john, it should return one single row

When the value of @input is 'john' the result is (e.g., in mysql client use SET @input = 'john'):

+------+-----------------------+
| name | occupations |
+------+-----------------------+
| john | Lawyer, Social worker |
+------+-----------------------+

ex: if someone search for social worker, it should return two rows

And, when the value of @input is 'social worker':

+------+-----------------------+
| name | occupations |
+------+-----------------------+
| jane | Social worker, Doctor |
| john | Lawyer, Social worker |
+------+-----------------------+