SELECT all rows where the primary key has a foreign key in either of two columns

[Some-Jackass]

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

[Ezzaral]

Why not just a simple join?

SELECT DISTINCT
	N.id,
	N.Alias,
        N.Weight
FROM
	Ninja N, Matchup M 
WHERE
	M.BlueCornerNinjaID = N.id
OR
	M.RedCornerNinjaID = N.id
ORDER BY
	N.Alias

[Some-Jackass]

Why not just a simple join?

SELECT DISTINCT
	N.id,
	N.Alias,
        N.Weight
FROM
	Ninja N, Matchup M 
WHERE
	M.BlueCornerNinjaID = N.id
OR
	M.RedCornerNinjaID = N.id
ORDER BY
	N.Alias

Hi Ezzaral,
Thanks for the reply. I tried a self join in the beginning, but it too a whole 17.625 seconds to run the query.
The method that I posted at the top of this thread runs it in 0.698 seconds.

I'm a bit confused as to why. I would also think that having a two nested queries returning a count(), should be slower that a simple join.

I made sure my primary key and foreign key DATA TYPES are infact all INT, and they are indexed.

Any ideas?

[Ezzaral]

Did you check the query plan on the query I posted to see if it was using the indexes?

You could also try a UNION ALL of the sets for each corner, but there again I would expect that to be slower than the simple inner join.

[urtrivedi]

Can u check how much time this query takes.

select 
	N.id,
	N.Alias,
        N.Weight
FROM
	Ninja N left outer join  Matchup Mb on 	Mb.BlueCornerNinjaID = N.id
left outer join  Matchup Mr on 	Mr.RedCornerNinjaID = N.id


WHERE ifnull(Mb.BlueCornerNinjaID ,0)+ifnull(Mr.BlueCornerNinjaID ,0)>0
ORDER BY
	N.Alias