How to retrieve all the rows where primary key value found in the other column in the row in MySql
You can (easily) do one-level relations, and then UNION the results. That's what would take to get rows related to temp_no = '2017-06' (and with a query which is SQL standard):

SELECT 
    * 
FROM
(
    -- Direct search
    SELECT 
        *
    FROM
        t AS t0
    WHERE
        t0.temp_no = '2017-06'

    UNION

    -- One level related
    SELECT
        t1.*
    FROM
        t AS t0
        JOIN t AS t1 ON t1.sno = t0.p_sno
    WHERE
        t0.temp_no = '2017-06'
) AS u
ORDER BY
    sno ;


Giving:


 sno | temp_no | first_party | second_party | p_sno
 --: | :------ | :---------- | :----------- | ----:
   5 | 2017-05 | Akhtar Ali  | Amir Khan    |  null
   6 | 2017-06 | Akhtar Ali  | Amir Khan    |     5
 

dbfiddle here

NOTE: I've replaced your p_sno = 0 values by nulls, which seems to me that better represents the meaning of this column.



You seem to want to find all descendants of a certain row, using a parent-child relationship between p_sno and sno. This is an indefinetly-deep multi-level hierarychy. If you use a database that supports recursive CTEs (such as MariaDB instead of MySQL) you can use the recursive CTE explained in: Check CTE to get all children (descendants) of a parent.

If your question is restricted to MySQL, you can use the tecnique described by
How to create a MySQL hierarchical recursive query, that would make you produce the following query for your temp_no = '2017-08' use case:

 SELECT
     t_sorted.*
 FROM
     (SELECT 
         * 
     FROM 
         t
     ORDER BY
         p_sno, sno
     ) AS t_sorted,
     (SELECT
         @pv := 
         (SELECT
             sno
         FROM
             t
         WHERE
             temp_no = '2017-08'
         )
     ) AS init
 WHERE
     -- for first row
     t_sorted.sno = @pv  
     -- recursive query
     OR (find_in_set(p_sno, @pv) &gt; 0 AND @pv := concat(@pv, ',', sno)) ;



 sno | temp_no | first_party | second_party | p_sno
 --: | :------ | :---------- | :----------- | ----:
   8 | 2017-08 | Amir Khan   | Saleem       |  null
   9 | 2017-09 | Amir Khan   | Saleem       |     8
  10 | 2017-10 | Amir Khan   | Saleem       |     9
 

dbfiddle here