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)
problem_log.PROBLEM_ID = '41'
when you wantproblem_log.PROBLEM = 'PM'
? – Mikael Eriksson Mar 25 '15 at 7:02