Query to populate a leaderboard with entry conditions-CodePudding

I have 3 tables:

PLAYER        (NAME)
ALL_GOALS     (TYPE, GROUP        , POINTS)
GOALS_REACHED (TYPE, EARNED_POINTS, PLAYER_NAME)

I need to write a query which gives me the leaderboard.

A player can enter the leaderboard if and only if he reaches at least one goal with GROUP='IMPORTANT'.

For example:

  -------------- 
 | PLAYER       |
  -------------- 
 | John         |
 | Bill         |
 | Bob          |
 | Willy        |
  -------------- 

  ------------------- 
 | ALL_GOALS         |
  ------------------- 
 | A, IMPORTANT, 100 |
 | B, IMPORTANT, 200 |
 | C, OTHER, 10      |
 | D, OTHER, 10      |
  ------------------- 

  -------------- 
 | GOALS_REACHED|
  -------------- 
 | A, 100, John |
 | B, 200, Willy|
 | C, 10, Bob   |
 | D, 10, Bob   |
  --------------

I'd like to see in my leaderboard only John and Willy

CodePudding user response：

Something like this? (Sample data in lines #1 - 13; query begins at line #14)

SQL> with
  2  all_goals (type, cgroup, points) as
  3    (select 'A', 'IMPORTANT', 100 from dual union all
  4     select 'B', 'IMPORTANT', 200 from dual union all
  5     select 'C', 'OTHER'    ,  10 from dual union all
  6     select 'D', 'OTHER'    ,  10 from dual
  7    ),
  8  goals_reached (type, earned_points, player_name) as
  9    (select 'A', 100, 'John'  from dual union all
 10     select 'B', 200, 'Willy' from dual union all
 11     select 'C',  10, 'Bob'   from dual union all
 12     select 'D',  10, 'Bob'   from dual
 13    )
 14  select r.player_name
 15  from goals_reached r join all_goals g on g.type = r.type
 16  where g.cgroup = 'IMPORTANT'
 17    and g.points > 0;

PLAYE
-----
John
Willy

SQL>