SQL - check if a value in a list does not exist in table and return boolean accordingly

Question

I have a simple database schema composed of 3 tables

User
id
name
matricule

Document
id
serial

User_Document (join table)
user_id
document_id

I want to check if all items of list( list of Document.serial) exists in the join table(User_Document) then return true If at least one not exists it should return false

There is my current query

SELECT CASE WHEN EXISTS (
SELECT *
FROM user_document ud
INNER JOIN  document d ON d.id= ud.document_id  
INNER JOIN  user u ON u.id = ud.user_id
where u.matricule='123'
and d.serial in ('#1' ,'#2' , '#3')
)
THEN TRUE 
ELSE FALSE 
END

This doesn't work because it will returns always true even if a single item of list doesn't exist in the join table

I am under PostgreSQL

Thank you very much

Answer 1

Aggregate serials in an array and compare with an array of the desired serials:

SELECT ARRAY(SELECT d.serial
             FROM user_document ud
             JOIN document d ON d.id = ud.document_id  
             JOIN "user" u ON u.id = ud.user_id
             WHERE u.matricule = '123') @> ARRAY['#1', '#2', '#3']::varchar[];

Note that I had to quote the table name for user in the query, since it's a reserved key word in PostgreSQL and the SQL standard.

Another approach is to count the distinct serials matching the list and check that the count matches the length of the list:

SELECT count(DISTINCT d.serial) = 3
FROM user_document ud
JOIN document d ON d.id= ud.document_id  
JOIN "user" u ON u.id = ud.user_id
WHERE u.matricule='123' AND d.serial IN ('#1','#2','#3');

This version also works with databases that do not support arrays (such as MySQL), and might be more efficient if there is a large number of documents related to the user.

Answer 2

SELECT
D.*,
(CASE WHEN (SELECT 1 FROM USER_DOCUMENT 
WHERE D.ID = UD.DOCUMENT_ID LIMIT 1) = 1 THEN TRUE ELSE FALSE END)
FROM DOCUMENT D

Answer 3

Try this:

     SELECT bool_and(ud.document_id is not null) as all_match,
            bool_or(ud.documnet_id is not null) as at_least_one_matches
       FROM document d
  LEFT JOIN user_document ud ON d.id = ud.document_id;

This should go through, do a left join, and return true if all match, and false if one mismatches. The second returns true if at least one matches.

If you want to sho