CODEWITHSUNDEEP
sqljoin
Ivan
Ivan

Reputation: 315

SQL query, join multiple tables, Oracle

I have an assignment where I need to write a SQL Query that would retrieve the player number and the total amount paid in penalties for players who are captains and have paid more than £80 in total for penalties.

I've got three tables

Players (playerNo, name, initials, street, town, postcode)

Teams (teamNo, playerNo, division)

Penalties (paymentNo, playerNo, penDate, amount)

bold text indicates the primary key. The playerNo in Teams table indicates the team captain.

I don't know how to join three tables. But my attempt at this is:

SELECT p1.playerNo, SUM(p2.amount)
FROM Players p1 INNER JOIN Teams t
   ON p1.playerNo = t.playerNo 
JOIN penalties p2
   ON p2.playerNo = p1.playerNo
GROUP BY playerNo
HAVING SUM(p2.amount) > 80;

Is any of this correct?

Upvotes: 1

Views: 24183

Answers (4)

amr
amr

Reputation: 1

select captaion_number , sum (amount) total_amount 
from (select t.playes.no   captaion_number , p.amount   amount 
from teams t ,  Penalties p
 where t.player_no = p.player_no ) 
group by captaion_number 
having total_amount > 80 ;

Upvotes: 0

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115510

If a player cannot be a captain to more than one team, yes, your query is correct. It only needs you to replace GROUP BY playerNo with GROUP BY p1.playerNo.

But if a player can be the captain of more than one team, it will return wrong results. This will give you a correct answer, in any case:

SELECT p1.playerNo, SUM(p2.amount)
FROM Players p1 
  JOIN penalties p2
    ON p2.playerNo = p1.playerNo
WHERE EXISTS
      ( SELECT *
        FROM Teams t
        WHERE p1.playerNo = t.playerNo
      ) 
GROUP BY p1.playerNo
HAVING SUM(p2.amount) > 80;

Upvotes: 0

Hituptony
Hituptony

Reputation: 2860

SELECT a.playerNo, a.playerName, SUM(c.amount) as amount, b.teamNo as Team
FROM players a, teams b, penalties c
WHERE a.playerNo = b.playerNo and a.playerNo = c.playerNo
Group By a.playerNo, c.teamNo
Having sum....

Upvotes: 4

Gordon Linoff
Gordon Linoff

Reputation: 1269443

Your query appears to be correct to return what you want. It does open the question of what to do if the same player is captain of more than one team, but that probably does not happen.

Do you want comments on the database structure?

Upvotes: 1

Related Questions