在其他表格中获取数量

Question

我有两个表格EXERCISE和EXERCISEUSER。我需要列出所有锻炼项目，并在查询中添加一个附加字段，如果该锻炼存在于表格EXERCISEUSER中，将返回该字段。换句话说，如果用户做了这个练习，我需要知道。如果是这样，它将在EXERCISEUSER中有一排。

我当前的查询是：

SELECT 
    "E".*, 
    "T"."NAME" AS "LEVEL" 
FROM 
    "EXERCISE" AS "E" 
INNER JOIN 
    "EXERCISETYPE" AS "T" 
ON 
    E.STO_FK_EXERCISETYPEEXERCISE = T.PK_EXERCISETYPE 
INNER JOIN 
    "LEVEL" AS "L" 
ON 
    L.PK_LEVEL = E.STO_FK_LEVELEXERCISE 
WHERE 
    (
     E.STATUS = 1) 
AND (
     L.STATUS = 1) 
AND (
     L.PK_LEVEL = 5) 
ORDER BY 
    "T"."ORDER" ASC 

，我会提供PK_USER了。

谢谢！

---

那么，我使用子查询，并达到我想要的结果。

SELECT 
    "E".*, 
    "T"."NAME" AS "LEVEL", 
    (SELECT COUNT(*) FROM STOUSER.EXERCISEUSER AS EU WHERE EU.STO_FK_EXERCISEEXERCISEUSER = E.PK_EXERCISE AND EU.STO_FK_USEREXERCISEUSER = 5978) AS MAKE_EXER_NUM 
FROM 
    "STOUSER"."EXERCISE" AS "E" 
INNER JOIN 
    "STOUSER"."EXERCISETYPE" AS "T" 
ON 
    E.STO_FK_EXERCISETYPEEXERCISE = T.PK_EXERCISETYPE 
INNER JOIN 
    "STOUSER"."LEVEL" AS "L" 
ON 
    L.PK_LEVEL = E.STO_FK_LEVELEXERCISE 
WHERE 
    (
     E.STATUS = 1) 
AND (
     L.STATUS = 1) 
AND (
     L.PK_LEVEL = 5) 
ORDER BY 
    "T"."ORDER" ASC 

谢谢！

Answer 1

我认为这应该做一个左外部联接。