mysql查询返回多个值 - 应该返回一个

Question

I have a website where visitors can create "battles" and upload videos to compete in these battles.

The following mySQL query is trying to retrieve details of every "battle" in a database.

The problem I have is that if a "battle creator" has uploaded two "videos" to the same battle, a duplicate battle prints out.

How can I make the query only print out one value for each battle, even if the videos table has two entries under the same battle_id?

Thanks!

SELECT * from Battles, Player, Video 
WHERE (Battles.battle_creator = Player.player_id 
AND Battles.battle_id = Video.battle_id 
AND Video.player_id = Battles.battle_creator) 
ORDER BY Battles.battle_date DESC;

Answer 1

There's no way to get the information you're asking for from a single query once multiple videos have been assigned to a battle by a single user.

The best way to get all the data for the battle is to separate your query into two subqueries:

SELECT * from Battles, Player 
WHERE Battles.battle_creator = Player.player_id
ORDER BY Battles.battle_date DESC;

...and then:

SELECT * from Video 
ORDER BY Battles.battle_date DESC, Player.player_id;

The first query will give you one row per battle; the second will give you all videos for all battles, which you can iterate over.

From a scaling perspective, you'll do better to avoid JOINs altogether, so the extra work will be well worth it.