DB2 SQL multiple JOIN issue

I have a query with two JOIN and it does not work. I get no errors. It just does not return any records. If I separate my query then it works. What Am I doing wrong here?

When I split up the query I get one record each which is what I should get.

Full query:

SELECT HPOL07.*, @RFC.*, @AAM.*
FROM BPCSPROF.HPOL07
JOIN BPCSPROF.@RFC ON PRFC = @RFC.RFCNUM  AND PPRF = @RFC.RFCPRC
 AND PGLNO = @RFC.RFCGLN
JOIN BPCSPROF.@AAM ON PPRF = @AAM.AAMPRC  AND PGLNO = @AAM.AAMGLN
WHERE PORD = '605400' AND PID <> 'PZ'

Separate queries:

 SELECT HPOL07.*, @RFC.*
    FROM BPCSPROF.HPOL07
    JOIN BPCSPROF.@RFC ON PRFC = @RFC.RFCNUM  AND PPRF = @RFC.RFCPRC
    AND PGLNO = @RFC.RFCGLN
 WHERE PORD = '605400' AND PID <> 'PZ'


SELECT HPOL07.*, @AAM.*
   FROM BPCSPROF.HPOL07
   JOIN BPCSPROF.@AAM ON PPRF = @AAM.AAMPRC  AND PGLNO = @AAM.AAMGLN
WHERE PORD = '605400' AND PID <> 'PZ'

Solution

You're doing inner joins, so there must be a record in both @RFC and @AAM for each record in HPOL07...

Is that what you want?

If there's a matching record in @RFC or @AAM, then you'd want to use a LEFT OUTER JOIN

SELECT HPOL07.*, @RFC.*, @AAM.*
FROM BPCSPROF.HPOL07
LEFT OUTER JOIN BPCSPROF.@RFC 
  ON PRFC = @RFC.RFCNUM  AND PPRF = @RFC.RFCPRC
     AND PGLNO = @RFC.RFCGLN
LEFT OUTER JOIN BPCSPROF.@AAM 
  ON PPRF = @AAM.AAMPRC  AND PGLNO = @AAM.AAMGLN
WHERE PORD = '605400' AND PID <> 'PZ'