CODEWITHSUNDEEP
mysqlsqlselect
nawfal
nawfal

Reputation: 73183

SQL query to select from two tables with if condition

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.

Tables: Notification        Acknowledgment

fields: id, notifier        id, parent_id, status

Now I have to choose rows from Notification such that:

  1. there is no Acknowledgment WHERE Acknowledment.parent_id = Notification.id (basically no Acknowledgment for that particular Notification) //or
  2. 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

A pseudo SQL code: 

   "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..

Upvotes: 2

Views: 11077

Answers (3)

Akhil
Akhil

Reputation: 7600

  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)

Upvotes: 1

ig0774
ig0774

Reputation: 41277

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

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.

Upvotes: 3

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726639

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

Upvotes: 1

Related Questions