Selecting all rows from t1 join t2 on id and select the lowest value from t2 or null if not any-CodePudding

I have two tables, one holds some categories and the other holds players' records like so:

Categories           Times
id Name              id UserId MapId CategoryId Time
1  cat1              1  1      1     1          1500
2  cat2              2  3      1     2          3000
3  cat3              3  13     1     3          2500
4  cat4              4  12     1     4          1500
5  cat5              5  11     1     4          1000

I want to select all the categories (id, name) and the lowest time on each category. If there's no record on that category it should show NULL or 0. This would be the expected result:

Result
id Name Time
1  cat1 1500
2  cat2 3000
3  cat3 2500
4  cat4 1000
5  cat5 0

I'm using the following query, but it only selects the categories that already have a record in Times. For example, if I use the following query it'll not select 'cat5' because it doesn't have any record in table Times.

select t2.id, t2.Name, min(t1.Time) as Time
from Times t1
join Categories t2 on t2.id = t1.CategoryId 
where t1.MapId = %MAPID%
group by t2.id

CodePudding user response：

I recommend to begin your query with the table "categories" in this case since your focus is on the data from this table. So you could write a left join. Furthermore, I think it's a good idea to replace null values by zero, thus your query would as example find negative times as the lowest times and return 0 if the lowest time is a null value. Overall, this could be your goal:

SELECT c.id, c.name, MIN(COALESCE(t.time,0)) AS time
FROM categories c LEFT JOIN times t ON c.id = t.categoryid
GROUP BY c.id, c.name;

Here is a working example according to your sample data: db<>fiddle

There are likely also other options to achieve your goal, you can just try out.

CodePudding user response：

I think you might just need to do a right join (because you want all rows from the 2nd table listed -- Categories). See if you get the desired results by changing line 3 to be:

right join Categories t2 on t2.id = t1.CategoryId