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

`I want to check if all items of a document serial list exists in...` Do you mean: if one element does not exist (but the others do), it should return False? Please rephrase ... — wildplasser, Aug 30 '17 at 13:55

score 2 · Answer 1 · answered Aug 30 '17 at 11:55

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

answered Aug 30 '17 at 11:55

Piotr Rogowski

3,642
19
24

I need to compare from a existing document serial list not all entries in document. Thank you – ulquiorra Aug 30 '17 at 12:17

score 2 · Answer 2 · answered Aug 30 '17 at 12:32

2

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

answered Aug 30 '17 at 12:32

Chris Travers

25,424
6
65
182

This is almost the result I want except I want to filter through a defined list of document id and not the entire table (as shown in my example) thank you very much – ulquiorra Aug 30 '17 at 13:29
Then add a where clause. The aggregations work on rows which are not excluded by the where clause. – Chris Travers Aug 31 '17 at 12:29

markusk · Accepted Answer · 2017-09-01T07:58:35.337

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.

Thank you but i got a " operator does not exist: character varying[] @> text[] . No operator matches the given name and argument type(s). You might need to add explicit type casts. " :( — ulquiorra, Aug 30 '17 at 14:15
Added cast to `varchar[]`, please retry. Could you please update your question with the full DDL for your schema, including the types of each column? — markusk, Aug 30 '17 at 19:55

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

3 Answers3