php - MySQL从两个表中选择

Question

我有两张表，里面有一堆字段[只需要提到]

 - partners
  - id [PRI, int]
  - name [varchar(20)]
  - match [=matches.id]
  - can_call [int]  


 - players
  - registered [0/1]
  - partner [=partners.id]
  - match [=matches.id]

现在，我需要一个查询，它会给出一个 int – 一个伙伴可以调用多少玩家；

要了解任何合作伙伴为任何比赛召集了多少玩家，我使用:

SELECT COUNT(id)
FROM players
WHERE registered=1
  AND partner=PARTNER_ID
  AND match=MATCH_ID

现在，我需要这样的东西:

SELECT (can_call-THE_ABOVE) as free
FROM partners
WHERE match=MATCH_ID

我不知道该怎么做:)

想法是:为我们想要的比赛获得我们想要的合作伙伴的免费位置，然后减去 SELECT COUNT(id) 名球员，该球员已经被该合作伙伴注册了该比赛。

谢谢! [希望我说得有道理^_^]

编辑:我已经解决了(在两个加入答案之前)。我可能会采用我的解决方案，只是因为它更简单；但是一旦我想成为 MySQL 高级用户，我会更多地研究其他答案，谢谢大家 ^^

Answer 1

Select P.name
    , P.can_call - Coalesce(Calls.CallCount,0) As CanCall
From partners As P
    Left Join   (
                Select partner, match, Count(*) As CallCount
                From players As P1
                Where registered = 1
                Group By partner, match, 
                ) As Calls
        On Calls.partner = P.id
            And Calls.match = P.match
Where P.match = MATCH_ID