Oh, my title is not the best one and as English is not my main language maybe someone can fix that instead of downvoting if they've understood the issue here.
Basically i have two tables - tourneyplayers and results. Tourneyplayers is like a side table which gathers together tournament information across multiple tables - results, tournaments, players etc. I want to check duplicates from the results table over column day1_best, from single tournament and return all the tourneyplayers who have duplicates.
Tourneyplayers contain rows:
Tourneyplayers tp_id | resultid | tourneyid 1 | 2 | 91 2 | 21 | 91 3 | 29 | 91 4 | 1 | 91 5 | 3 | 92
Results contains rows:
Results: r_id | day1_best 1 | 3 2 | 1 3 | 4 .. | .. 21 | 1 .. | .. 29 | 2
Now tourney with id = 91 has in total 4 results, with id's 1,2,21 and 29. I want to return values which have duplicates, so currently the result would be
Result tp_id | resultid | day1_best 1 | 2 | 1 2 | 21 | 1
I tried writing something like this:
SELECT * FROM tourneyplayers WHERE resultid IN ( SELECT r1.r_id FROM results AS r1 INNER JOIN results AS r2 ON ( r1.day1_best = r2.day1_best ) AND ( r1.r_id <> r2.r_id ) ) AND tourneyid =91
But in addition to values which had the same day1_best it chose two more which did not have the same. How could i improve my SQL or rewrite it?
JOIN both tables, so you know how the data looks like.
SELECT * FROM tourney_players t JOIN results r ON t.`resultid` = r.`r_id`;
Then using the same query you
GROUP to see what
tourneyid, day1_best combination has multiple rows
SELECT `tourneyid`, `day1_best`, count(*) as total FROM tourney_players t JOIN results r ON t.`resultid` = r.`r_id` GROUP BY `tourneyid`, `day1_best`;
Finally you use the base
JOIN and perform a
LEFT JOIN to see what rows has a match and show only those rows.
SELECT t.`tp_id`, r.`r_id`, r.`day1_best` FROM tourney_players t JOIN results r ON t.`resultid` = r.`r_id` LEFT JOIN (SELECT `tourneyid`, `day1_best`, count(*) as total FROM tourney_players t JOIN results r ON t.`resultid` = r.`r_id` GROUP BY `tourneyid`, `day1_best` HAVING count(*) > 1) as filter ON t.`tourneyid` = filter.`tourneyid` AND r.`day1_best` = filter.`day1_best` WHERE filter.`tourneyid` IS NOT NULL;