If there is a Fee on Class Level then that fee overrides Fee on Course Level 1

[kernal]

The fees highlighted in red are the ones I need returned in the SELECT statement. Some classes will only have a "CLASS" or "COURSE" level but some will have both and if they have both then I only want to return the "CLASS" level.

Class Level Type Nbr Amt
[highlight #EF2929]TEST 101-1 CLASS 300001 $200[/highlight]
TEST 101-1 COURSE 300001 $300

[highlight #EF2929]TEST 102-1 COURSE 300002 $200[/highlight]

Help is appreciated!!!

 

[carp]

This is what came to mind over morning coffee - others may have a more efficient approach. Not tested, but this should work:

SELECT Class, Level, Type, Nbr, Amt FROM fee_table WHERE type = 'CLASS'
UNION ALL
SELECT Class, Level, Type, Nbr, Amt FROM fee_table f
       INNER JOIN (SELECT nbr FROM fee_table WHERE type = 'COURSE'
                   MINUS 
                   SELECT nbr FROM fee_table WHERE type = 'CLASS') v
          ON v.nbr = f.nbr;

Hopefully, you have an index on your TYPE column.

 

[kernal]

Thanks Carp! I will try it tomorrow.

 

[kernal]

Hi carp,

It worked great! I really appreciate your help! Have a good day and be safe!