行是否存在且存在多个where

Question

我刚刚问了这个问题多个Where条件，但意识到还有更多内容（并且没有想要混淆另一个问题）。

我有一个看起来像这样的表格：

meta_id - id - meta_key     - meta_value
1         1    school         Some School 1
2         2    school         Some School 2
3         2    hidden         1
4         3    school         Some School 3
5         4    school         Some School 4
6         5    school         Some School 5
7         5    hidden         1

由于我之前的问题，我有这样的语法：

SELECT DISTINCT m1.id 
FROM metadata m1
join metadata m2 on m1.id = m2.id
WHERE (m1.meta_key = 'school' AND m1.meta_value = 'Some School 1') 
AND (m2.meta_key = 'hidden' AND m2.meta_value = '1')

如果 school = Some School 1 和 hidden = 1 则找到 id

但是问题是，hidden 行仅在隐藏时才存在，如果不隐藏则不存在 - 所以它不像 hidden = 0 意味着它不存在那么简单隐藏和 hidden = 1 表示它是隐藏的。

因此，我需要找出 school = Some School 1 并且对于相同的 id，行 hidden 不存在。

Answer 1

SELECT DISTINCT m1.id 
FROM metadata m1
WHERE 
    m1.meta_key = 'school' 
AND m1.meta_value = 'Some School 1' 
AND NOT EXISTS 
(
  SELECT * FROM metadata m2
  WHERE 
      m2.meta_key = 'hidden' 
  AND m2.meta_value = '1'
  AND m2.Id = m1.Id
)