Swarnim Kumar
Swarnim Kumar

Reputation: 375

How to use exists function in sql query?

Select EXISTS (adm.document_id) as documentUploaded, name,customer_id as customerId,
adm.id,document.id as documentId from 
document,application_document_mapping as adm where
document.deleted=false and document.id=adm.document_id and application_id=1;

The above query gives a syntax error. Not able to understand why?

Upvotes: 0

Views: 44

Answers (2)

Laurenz Albe
Laurenz Albe

Reputation: 248305

EXISTS is not a function. It has to contain a subquery, that is, something that starts with SELECT. EXISTS returns TRUE if the subquery has at least one result row, else FALSE.

You can either use EXISTS like in Zaynul's answer or use an outer join:

SELECT adm.document_id IS NULL AS documentuploaded,
       name,
       customer_id AS customerId,
       adm.id,
       document.id AS documentId
FROM document
   LEFT JOIN application_document_mapping AS adm
      ON document.id = adm.document_id AND adm.application_id = 1
WHERE NOT document.deleted;

Upvotes: 1

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32031

below is an way

 select t1.* from table document t1
 where exists ( select 1 from application_document_mapping t2 where
                                      t1.id=t2.document_id 
                                      and application_id=1)

Upvotes: 1

Related Questions