MySQL Join并获得所有关系，即使为0 [英] MySQL Join and get all relations even if 0

问题描述

我有两张桌子.一种持有所有可用的奖杯，另一种持有使用者和奖杯之间的关系.

I got two tables. One that holds all availeble trophys and one that holds the relation between user and trophy.

trophy
--------------------------------------
| trophy_id | name                   |
--------------------------------------
| 1         | kill 100 people        |
| 2         | kill 200 people        |
| 3         | fly 5000 feet upwards  |
| 4         | fly into a mountain    |
--------------------------------------

earned_trophys
------------------------------------------
| earned_trophy_id | trophy_id | user_id |
------------------------------------------
| 1                | 1         | 3       |
| 2                | 1         | 2       |
| 3                | 3         | 4       |
| 4                | 2         | 1       |
| 5                | 3         | 1       |
------------------------------------------

例如 用户1获得了杀死100个人和杀死200座奖杯的奖励.

For example User 1 has earned the kill 100 people and kill 200 people trophys.

我想要一个显示如下内容的查询:

I want a query that shows me something like this:

for user 1
-----------------------------
| kill 100 people       | 1 |
| kill 200 people       | 1 |
| fly 5000 feet upwards | 0 |
| fly into a mountain   | 0 |
-----------------------------

这是我尝试过的:

select
    trophy.name,
    earned_trophys.user_id,  
    count(user_id) as temp
from
    trophy
left join
    earned_trophys
on
    trophy.trophy_id = earned_trophys.trophy_id
where
    earned_trophys.user_id = 1
group by
    name

但是我只得到用户得到的结果，我希望temp = 0行. 可以在一个查询中做到这一点吗?

but I only get the results for the things the user got, I want the temp = 0 rows to. is it possible to do this in one query?

推荐答案

要使左连接生效，您需要将条件earned_trophys.user_id = 1移到on子句中，而不是where.

for left join to be in effect you need to move condition earned_trophys.user_id = 1 into the on clause instead of where.

select
    trophy.name,
    earned_trophys.user_id,  
    count(user_id) as temp
from
    trophy
left join
    earned_trophys
on
    trophy.trophy_id = earned_trophys.trophy_id and earned_trophys.user_id = 1
group by
    name

这篇关于MySQL Join并获得所有关系，即使为0的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！