I have a 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'
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)