shader2199 shader2199 - 17 days ago 5
MySQL Question

List top 10 Results between Two Tables

I am currently having an issue with trying to list the top 10 rows between two tables. Table one has columns (name, gold, id), and table two has columns (name, gold, id).

How would I list the top 10 gold between the two tables and prevent a duplicate row from being listed?

Both tables have nearly identical data. After a period of time, one table updates the other table, kinda like a backup table. But sometimes table2's data can be different than table1's data until the update takes place.

Say table1 has rows like...

(Josh, 500, 1),
(Bob, 600, 2),
(Jim, 687, 3),
(John, 777, 4),
(Jack, 888, 5)


and table2 has rows like...

(Josh, 488, 1),
(sue, 1000, 6),
(Joe, 6882, 7),
(John2, 77, 8),
(Jay, 874, 9)


How would I list the top 10 gold between the two tables without showing duplicate rows? I want table2's data to take priority over table1. I am currently using this method...

SELECT table1.gold AS mysum, table2.gold AS mysum2
FROM table1
LEFT JOIN table2 ON table2.id=table1.id
ORDER BY mysum DESC
LIMIT 0, 10


then I am just checking the results of of this query and using either mysum or mysum2 depending on if mysum2 is set.

This doesn't exactly work the way I want though because if mysum2 is set, it will still be listed as a top 10 result of table1 and be in an incorrect position on the list.

Answer

Using IFNULL.

SELECT t.g FROM
(
  SELECT IFNULL(table2.gold, table1.gold) AS g
  FROM table1 
  LEFT JOIN table2 ON table2.id=table1.id 
) t
ORDER BY t.g DESC 
LIMIT 0, 10
Comments