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;
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.
As Ninsuo's comment points out, the proper way to control this is, after your ORDER BY clause, specify LIMIT 1.
This won't work if you want the entire table, just without duplicates. Consider running some comparison checks on your returned data, or using SELECT DISTINCT.
You can either add LIMIT 1 clause to your query to only get first result, or use DISTINCT clause like
SELECT DISTINCT *
FROM ...
That said, you should not use "SELECT * " when querying for more than one table - use "SELECT table.*" or "SELECT table.field1, table.field2, ..." to be more specific.
You can't do "exactly" that, because your query:
SELECT * from Battles, Player, Video ...
is implicitly asking for all the videos. So you need to ask yourself first, how do I select that one video I want?
If you just want one video, whatever, then add LIMIT 1 to the query and be done with that. ORDER BY video_date ASC or DESC before the LIMIT to retrieve the earliest or latest video.
Otherwise, you have to do something like:
SELECT * from Battles
JOIN Player ON (Battles.battle_creator = Player.player_id)
JOIN Video ON (Battles.battle_id = Video.battle_id
AND Video.player_id = Battles.battle_creator)
WHERE Video.video_id = (SELECT MIN(video_id) FROM Video AS Video2 WHERE
Battles.battle_id = Video2.battle_id
AND Video2.player_id = Battles.battle_creator)
ORDER BY Battles.battle_date DESC;
In the example above I used, as "video choice criterion", "the video with smallest video_id". You will want to have an index on (Video.video_id), something like
CREATE INDEX video_ndx ON Video(player_id, battle_id, video_id);