CODEWITHSUNDEEP
mysqlsqldatabase-design
Michael Abraham
Michael Abraham

Reputation: 57

SQL Query to return rows where a column value appears multiple time

I'm creating a simple database which will allow me to track snooker results, producing head to head results between players. Currently I have 3 tables: (Player, Fixture, Result)

PlayerID    PlayerName
1           Michael Abraham
2           Ben Mullen
3           Mark Crozier

FixtureID   Date                 TableNo    Group
1           07/12/2015 19:00:00  12         0
2           08/12/2015 12:00:00  9          0

ResultID    FixtureID   PlayerID    FramesWon
1           1           1           3
2           1           3           1
3           2           1           5
4           2           2           1

I would like a query which returns all rows in the result table for fixtures which took place between players 1 and 3. Currently my query is: 

SELECT *
FROM Result
WHERE PlayerID IN (1,3);

This returns the first 3 rows of the result table - when I'm only looking for the top 2 rows because they share the same FixtureID. Is there an easy way to remove the third row from this query result, or should I reconsider my database design? Any help would be appreciated.

Upvotes: 1

Views: 157

Answers (2)

Michael
Michael

Reputation: 3729

You could join your fixtures table twice, like this:

select
  *
from
  Result as R1
  join Result as R2 on R1.FixtureID = R2.FixtureID
where
  R1.PlayerID in (1,3)
  AND R2.PlayerID in (1,3)
  AND R1.PlayerID != R2.PlayerID
group by
  R1.FixtureID
;

Or, since it's a bit messy now, show it like a snooker score display often is shown:

select
  R1.FixtureID, R1.PlayerID as player1, R1.FramesWon as player1_frames, R1.FramesWon+R2.FramesWon as total_frames, R2.FramesWon as player2_frames, R2.PlayerID as player2
from
  Result as R1
  join Result as R2 on R1.FixtureID = R2.FixtureID
where
  R1.PlayerID in (1,3)
  AND R2.PlayerID in (1,3)
  AND R1.PlayerID != R2.PlayerID
group by
  R1.FixtureID
;

Upvotes: 0

fthiella
fthiella

Reputation: 49089

One solution is to use a GROUP BY query, grouping by FixtureID and counting the rows for each FixtureID. This query will select all FixtureIDs with both players 1 and 3:

select
  FixtureID
from
  Results
where
  PlayerID IN (1,3)
group by
  FixtureID
having
  count(*)=2

then to get the record from the Results table you can use this query:

select *
from Results
where FixtureID IN (
  select FixtureID
  from Results
  where PlayerID IN (1,3)
  group by FixtureID
  having count(*)=2
)

Upvotes: 1

Related Questions