MySQL ID 存在于此表或该表中 - mysql id exists in this table or that table

mysql id exists in this table or that table

本文关键字：于此表 ID 存在 MySQL | 更新日期: 2023-09-27

我正在查询一个mysql db。我有3张桌子：

官员

id | first | last
------------------
1  | jim   | smith
2  | john   | Doe
3  | larry  | toad
4  | Dave   | Charles

游戏

id | gm_date    | league | REF | UMP
--------------------------------
1  | 2015-03-27 | 1      |   4 | 
2  | 2015-03-28 | 1      |     | 
3  | 2015-03-29 | 1      |     | 
4  | 2015-04-01 | 1      |     | 2

事件

id | date       | league | off | 
--------------------------------
1  | 2015-03-27 | 1      | 1   | 
2  | 2015-03-28 | 1      |     | 
3  | 2015-03-29 | 1      |     | 
4  | 2015-04-01 | 1      |     |

我想查询游戏表和事件表的查询，以查看给定日期的 id 是否存在。如果它在一个或两个中，我想返回 true。

这是我到目前为止的查询，但它似乎不起作用。

SELECT id , first , last
                    FROM officials AS o
                    WHERE o.id = 4 &&
                    EXISTS (
                    SELECT *
                    FROM games AS g
                    WHERE g.REF = o.id && g.gm_date = '2015-03-27' && g.league = 1
                    ) ||
                    EXISTS (
                    SELECT *
                    FROM events as e
                    WHERE e.off = o.id && e.date = '2015-03-27' && e.league = 1
                    )

它返回日期的所有 ID，而不是我请求的 ID。

任何帮助将不胜感激。我希望我描述得足够好？

也许是这样的：

SELECT 
    id , 
    first , 
    last
FROM officials AS o
WHERE o.id = 4 
AND
(
    SELECT 
        NULL 
    FROM 
        games AS g
    WHERE 
        g.REF = o.id 
        AND g.gm_date = '2015-03-27' 
        AND g.league = 1
    UNION ALL
    SELECT
        NULL
    FROM 
        events as e
    WHERE 
        e.off = o.id 
        AND e.date = '2015-03-27' 
        AND e.league = 1
)

也许：你没有在游戏表中查看 UMP 似乎很奇怪。我使用了联接存在，以防您需要返回有关游戏或事件的其他数据。

SELECT o.id, o.first, o.last
FROM officials o
LEFT JOIN Games g
  on g.Ref = O.id
 and g.gm_date = '2015-03-27'
 and g.league = 1
LEFT JOIN Events e
  on e.Off = O.Id
 and e.league = 1
 and e.date = '2015-03-27'
GROUP BY o.id, o.first, o.last

我猜你想要所有 id 为 4 的官员，其中相应的行存在于games或events中.如果是这样的话，那么你需要将存在条件括起来，因为如果没有括号，查询将返回官方 ID 为 4 且games中存在相应行的所有行，以及官方（具有任何 ID）具有相应行的所有行events

SELECT id , first , last
FROM officials AS o
WHERE o.id = 4 &&
(
    EXISTS (
        SELECT *
        FROM games AS g
        WHERE g.REF = o.id && g.gm_date = '2015-03-27' && g.league = 1
    ) || EXISTS (
        SELECT *
        FROM events as e
        WHERE e.off = o.id && e.date = '2015-03-27' && e.league = 1
    )
)

我认为您被AND和OR逻辑运算符之间的优先级顺序所困扰。

SELECT  0 AND 1  OR 1
     , (0 AND 1) OR 1
     ,  0 AND (1 OR 1)

通过在 AND/OR 条件周围添加括号，您可以显式指定要评估它们的优先级顺序。

这是似乎有效的查询。

   SELECT 
       id , 
       first , 
       last
   FROM officials AS o
   WHERE o.id = 4 
   AND
   (
       SELECT 
           g.REF 
       FROM 
           games AS g
       WHERE 
           g.REF = o.id 
           AND g.gm_date = '2015-03-27' 
           AND g.league = 1
       UNION ALL
       SELECT
            e.off
       FROM 
           events as e
       WHERE 
           e.off = o.id 
           AND e.date = '2015-03-27' 
           AND e.league = 1
   )