SQL查询从两个表中选择if条件

I have two tables Notification and Acknowledgment. Acknowledgment has a field which holds the primary key of Notification table. Basically a Notification will have many Acknowledgments.

我有两个表通知和确认。确认有一个字段，其中包含Notification表的主键。基本上，通知会有许多致谢。

Tables: Notification        Acknowledgment

fields: id, notifier        id, parent_id, status

Now I have to choose rows from Notification such that:

现在我必须从Notification中选择行，以便：

there is no Acknowledgment WHERE Acknowledment.parent_id = Notification.id (basically no Acknowledgment for that particular Notification) //or
没有确认WHERE Acknowledment.parent_id = Notification.id（基本上没有特定通知的确认）//或
if there is an Acknowledgment for Notification, then select Notification if any of the Acknowledgments with parent_id = Notification.id has a Acknowledgment.status = someValue
如果存在通知确认，则选择通知，如果任何具有parent_id = Notification.id的确认具有Acknowledgment.status = someValue

A pseudo SQL code:

一个伪SQL代码：

   "SELECT * FROM Notification (WHERE id is not present in Acknowledgment.parent_id) OR
   (WHERE id is present in Acknowledgment.parent_id AND Acknowledgment.status=@someValue"

I can break it into simpler queries and achieve this, but I would love to know one single query to get this done..

我可以将它分解为更简单的查询并实现这一点，但我很想知道一个单一的查询来完成这个任务。

3 个解决方案

#1

  SELECT    * 
    FROM    Notification n
            LEFT OUTER JOIN Acknowledgment a ON a.parent_id = n.id
  WHERE     (a.parent_id IS NULL OR a.status = @somevalue)

#2

SELECT n.* FROM Notification n
LEFT OUTER JOIN Acknowledgment a ON a.parent_id=n.id
WHERE a.status IS NULL OR a.status=@someValue

#3

As an alternative to LEFT OUTER JOINS, you can use the EXISTS clause.

作为LEFT OUTER JOINS的替代方法，您可以使用EXISTS子句。

For your example:

对于你的例子：

SELECT *
FROM Notification n
WHERE NOT EXISTS (
   SELECT *
   FROM Acknowledgement
   WHERE parent_id = n.id
) OR EXISTS (
   SELECT *
   FROM Acknowledgement
   WHERE parent_id = n.id AND status = @someValue
)

JOINs tend to be better optimized in SQL (particularly where, as in my example, you are using more than one on the same table), I just mention this alternative as it is a more direct translation of your pseudo-query.

JOIN往往在SQL中得到更好的优化（特别是在我的例子中，你在同一个表中使用多个），我只提到这个替代方案，因为它是你的伪查询的更直接的翻译。

#1

  SELECT    * 
    FROM    Notification n
            LEFT OUTER JOIN Acknowledgment a ON a.parent_id = n.id
  WHERE     (a.parent_id IS NULL OR a.status = @somevalue)

#2

SELECT n.* FROM Notification n
LEFT OUTER JOIN Acknowledgment a ON a.parent_id=n.id
WHERE a.status IS NULL OR a.status=@someValue

#3