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:
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