CODEWITHSUNDEEP
sqloracle-database
EmmetOT
EmmetOT

Reputation: 582

Trouble with nested inner joins

I have three tables that look like this:

_CONTRACT____________________
|Game_ID|Publisher_ID|Amount|
-----------------------------

Game_ID and Publisher_ID are together the primary key - games can be financed by multiple
publishers and publishers can finance multiple games.

_GAME____
|ID|Name|
---------

PUBLISHER
|ID|Name|
---------

I'm trying to make a query that selects a table which shows the most significant publisher's name (ie whichever contributed the most in the 'Contract' table) alongside the game name and the amount contributed.

MOST_SIGNIFICANT_PUBLISHER___
|Game|Publisher|Contribution|
-----------------------------

So far, I've gotten as far as having a table with just the most significant publisher. Ironically it's the easier part, just getting the names in there instead of the IDs, that I'm having trouble with.

So here's the select for the table of most sig publishers WITH IDs and not names:

SELECT conTable.*
FROM Contract conTable
INNER JOIN
(
SELECT Game_ID, MAX(Amount) AS MaxAmount
FROM Contract
GROUP BY Game_ID ) grouped
ON conTable.Game_ID = grouped.Game_ID
AND conTable.Amount = grouped.MaxAmount;

Here's my attempt at the next step, and the error it produces:

  1  SELECT g.*, p.*, c.*
  2  FROM Game g, Publisher p, Contract c
  3  INNER JOIN
  4  (
  5     SELECT conTable.*
  6     FROM Contract conTable
  7     INNER JOIN
  8     (
  9         SELECT Game_ID, MAX(Amount) AS MaxAmount
 10         FROM Contract
 11         GROUP BY Game_ID) grouped
 12     ON conTable.Game_ID = grouped.Game_ID
 13     AND conTable.Amount = grouped.MaxAmount ) newtable
 14  ON g.ID = newtable.Game_ID
 15  AND p.ID = newtable.Publisher_ID;
AND p.ID = newtable.Publisher_ID
    *
ERROR at line 15:
ORA-00904: "P"."ID": invalid identifier

I'm new to this and can't see what's wrong with my names. (Publisher does have an ID column, Game does have an ID column.)

Upvotes: 1

Views: 44

Answers (1)

sstan
sstan

Reputation: 36483

As pointed out in the comments, you are mixing join syntaxes, and that can lead to trouble. Not to mention that your join nesting is getting out of hand.

With the help of the row_number analytic function, the query can be simplified to:

select g.name as game_name,
       p.name as publisher_name,
       c.amount as contribution
  from game g
  join (select game_id,
               publisher_id,
               amount,
               row_number() over (partition by game_id order by amount desc) as rn
          from contract) c
    on c.game_id = g.id
   and c.rn = 1 -- this condition is what limits the results to the most significant contributor for a game
  join publisher p
    on p.id = c.publisher_id

Upvotes: 1

Related Questions