mardi 24 mars 2015

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


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 PM 3
S1G166901 Service 4
S1G166901 Other Hardware 1
S1G166901 Error on Hardware 2


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


like this



TERM PROBLEM PROBLEM_ID PGROUP
S1G166901 PM 3 Hardware
PM 3 Hardware
Other hardware 1 Hardware
Error on hardware 2 Hardware
S1G166902 PM 3 Hardware
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 = 'PM' then will not show but if contain will show all problem with pgroup ='Hardware' by term





Aucun commentaire:

Enregistrer un commentaire