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>

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

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 |
+------+-----------------------+