I need to extract all NOTICE_IDs from the table NOTICES where the 'removed' value is set to 0, but all the rows in table LINKS with that matching ID are set to 1. Grateful for any advice!

The relationship of these two tables is 1:N (NOTICES:LINKS)

Here is a data sample of the case I need to extract:

NOTICES
notice_id, removed
358902, 0

LINKS
link_id, notice_id, removed
147, 358902, 1
148, 358902, 1
149, 358902, 1

CodePudding user response：

This query is likely the most expressive solution for your task:

SELECT n.NOTICE_ID
FROM NOTICES n
WHERE n.REMOVED = 0
  AND ALL (SELECT l.REMOVED FROM LINKS l WHERE l.NOTICE_ID = n.NOTICE_ID) = 1

If LINKS.REMOVED can only be 0 or 1, then you can look for not existence of REMOVED = 0

SELECT n.NOTICE_ID
FROM NOTICES n
WHERE n.REMOVED = 0
  AND NOT EXISTS (
    SELECT * 
    FROM LINKS l 
    WHERE l.NOTICE_ID = n.NOTICE_ID 
      AND l.REMOVED = 0
  )

An equivalent query can be written using an anti-join:

SELECT n.NOTICE_ID
FROM NOTICES n
LEFT JOIN LINKS l 
  ON  l.NOTICE_ID = n.NOTICE_ID 
  AND l.REMOVED = 0
WHERE l.NOTICE_ID IS NULL

Note that all the above queries will also return NOTICE_IDs which have no links at all, because there is no link that has REMOVED = 1.

If you want that at there must be at least one LINK then you can use this one:

SELECT n.NOTICE_ID
FROM NOTICES n
JOIN LINKS l ON l.NOTICE_ID = n.NOTICE_ID 
HAVING BIT_AND(l.REMOVED) = 1 -- all and at least one must be 1/TRUE least one