Hi all.
I have a database table containing the details of, lets say, Ninjas. It has a primary key called NinjaID.
I have a second table containing a match-up of which Ninjas will be fighting eachother.
This table has 4 Columns:
1.) MatchID
2.) BlueCornerNinjaID
3.) RedCornerNinjaID
4.) DateTime
What I need is to extract a list of Ninja's from the Ninja Table, only of Ninjas that are scheduled to fight in a match (any match) regardless of whether it will be fighting from the blue corner or from the red corner.
For argument sake, lets say this is a super long list of Ninjas, so the query has to be relatively efficient.
I have a solution, but it feels like there must be a more elegant solution.
SELECT
Ninja.id,
Ninja.Alias,
Ninja.Weight
FROM
Ninja
WHERE
(SELECT count(*) FROM matchup WHERE matchup.BlueCornerNinjaID = Ninja.id) > 0
OR
(SELECT count(*) FROM matchup WHERE matchup.RedCornerNinjaID = Ninja.id) > 0
ORDER BY
Ninja.Alias