0

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)
  • The master, detail and results tables seem inconsistent. Can you check, please, and correct if needed. – Michael Green Mar 25 '15 at 4:58
  • i edited @MichaelGreen – Touchy Vivace Mar 25 '15 at 6:33
  • Why are you querying for problem_log.PROBLEM_ID = '41' when you want problem_log.PROBLEM = 'PM'? – Mikael Eriksson Mar 25 '15 at 7:02
  • @MikaelEriksson sorry for that, it's remain the same things because id is identity, so now I change to the same as querying for `PROBLEM_ID = '41' – Touchy Vivace Mar 25 '15 at 7:04
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
)
  • i try your code it's work like my code it's still show a value that not contain 'PM', first check is record have a problem = 'PM' then check other problem with a pgroup = 'Hardware' – Touchy Vivace Mar 25 '15 at 6:49
  • 1
    @TouchyVivace This one works when using your sample data. SQL Fiddle. – Mikael Eriksson Mar 25 '15 at 7:11
  • @MikaelEriksson thanks for prove me that, there's will my own way to solve and check my database fault :) – Touchy Vivace Mar 25 '15 at 7:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.