check if found specific record then find another record with the same group

Question

I have a table

Master table

PROBLEM_ID  PROBLEM            PGROUP
1           Other Hardware     Hardware
2           Error On hardware  Hardware
3           PM                 Hardware
4           Service            Maintenance

and a detail

TERM          PROBLEM            PROBLEM_ID
S1G166901     PM                    3
S1G166902     PM                    3
S1G166903     Service               4
S1G166902     Other on hardware     1
S1G166901     Service               4
S1G166901     Other Hardware        1
S1G166901     Error on Hardware     2

i need to query if PROBLEM_ID = '3' then find a PROBLEM that have PGROUP = 'Hardware'

like this

TERM          PROBLEM            PROBLEM_ID    PGROUP
S1G166901     PM                    3          Hardware
S1G166901     Other hardware        1          Hardware             
S1G166901     Error on hardware     2          Hardware
S1G166902     PM                    3          Hardware
S1G166902     Other Hardware        1          Hardware

i try to use Outer apply but it's return too much row

my specific result is if they're not contain PROBLEM_ID = '3' then will not show but if contain will show all problem with pgroup ='Hardware' by term

i try this

SELECT p.TERM, p.Problem_ID,p.problem, PGROUP
FROM Problem_log AS p 
CROSS APPLY ( 
            SELECT s.pgroup, s.problem_id 
            FROM problem AS s 
            WHERE   s.problem_id = p.problem_id AND PGROUP = 'Hardware'  )as s

            WHERE  exists (Select s.PGROUP,s.PROBLEM_ID 
            from PROBLEM_LOG 
            WHERE problem_log.PROBLEM_ID = '3'  GROUP BY term) 
            ORDER BY TERM

my sequence is - Select data from table problem_log for problem_ID = '3'

• if problem_ID = '3' then For each record check found other problem (Hardware only)

Answer 1

try this

SELECT p.TERM, p.Problem_ID,p.problem, PGROUP
FROM Problem_log p
INNER JOIN problem AS s
ON s.problem_id = p.problem_id
WHERE s.PGROUP IN
(
    SELECT PGROUP
    FROM Problem_log
    INNER JOIN problem
    ON Problem_log.problem_id = problem.problem_id
    WHERE Problem_log.PROBLEM_ID = 3
)