Double self join help

[eerich]

Hi everyone,

I need help with creating a double self join. I am attempting to pull together a security record where there are multiple security types and I need them to all layout in one row. I'm able to get a single self join to work perfectly, however in attempting to get the double self join I can't get the expected results. An example of expected output is:

UserID || Program || Type1 || Type2 || Type3
Bart || Check Print || 4 || 2 || B

Select Distinct F1.UserID, F1.Program, F1.Type1, F2.Type1 AS Type2, 
F3.Type1 AS Type3
From (FxnSec as F1 Inner Join FxnSec as F2 on (F1.UserID = F2.UserID AND 
F1.Program = F2.Program)) Inner Join FxnSec as F3 ON (F1.UserID = 
F3.UserID AND F1.Program = F3.Program)
Where (F1.Type1 = '4' AND F2.Type1 = '2') OR (F1.Type1 = '4' AND F3.Type1 = 'B')

The above does not give expected output as it generates two records instead of just one line as expected.

Any help is appreciated.

 

[eerich]

Sorry I left out one additional detail. There will need to always be at least two conditions met, and, in some cases all three conditions will be met, i.e., security types exists for each column. I believe that is the part that I can't figure out how to code correctly.

Thanks.