SQL Not Exists query

Question

I have problem integrating NOT EXISTS in my sql query. Let me explain the problem in detail I have four tables : branch_details , transactions,branch_order_relation and branch_pincode_relation.

Here is my SQL query

   private static final String SELECT_ORDERS_BY_BRANCH = 
"select transaction_id,source_id,destination_id 
from transactions,branch_pincode_relation,branch_details,branch_order_relation 
where branch_details.branch_email = ? 
and branch_details.branch_id = branch_pincode_relation.branch_id 
and branch_pincode_relation.origin_pincode = transactions.start_pin 
and transactions.parent_transaction_id IS NOT NULL 
and transactions.order_status = "+JiffieConstants.PAYMENT_SUCCESS;

There are some transaction_id of transactions table which exists in branch_order_relation (as order_id). So if the transaction_id is present in branch_order_relation we should not select it. Otherwise we need to select it. Can anyone please integrate this in the above SQL query. I tried and google also but not able to come up with solution

With SQL Server you should be using ANSI join syntax (`…from table1 t1 inner join table2 t2 on t1.col = t2.col…`) — Richard, May 08 '15 at 08:59
for starters I would go and read up on ANSI join syntax and reformat your query to use it — ninesided, May 08 '15 at 08:59
You can use left join on transaction_id and in where condition use branch_order_relation.transaction_id is null — Anil, May 08 '15 at 09:00
Since that is also a built up SQL statement, please read up on [parametrised sql statements](http://stackoverflow.com/questions/910465) and how to use them. — Bernd Linde, May 08 '15 at 09:09

score 1 · Accepted Answer · answered May 08 '15 at 09:29

I modified the query as

private static final String SELECT_ORDERS_BY_BRANCH = "select transaction_id,source_id,destination_id from transactions,branch_pincode_relation,branch_details where branch_details.branch_email = ? and branch_details.branch_id = branch_pincode_relation.branch_id and branch_pincode_relation.origin_pincode = transactions.start_pin and transactions.parent_transaction_id IS NOT NULL and transactions.order_status = "+JiffieConstants.PAYMENT_SUCCESS+" and NOT EXISTS (select null from branch_order_relation where branch_order_relation.order_id = transactions.transaction_id)";

and its working. Thanks!

please let me know if any bug is there – user3681970 May 08 '15 at 09:29 — user3681970, May 08 '15 at 09:29

score 0 · Answer 2 · answered May 08 '15 at 09:17

Use left outer join on transactions and branch_order_relation and add where clause branch_order_relation.order_id is null.

This will pick transactions which are not in branch_order_relation , see below

select transaction_id,source_id,destination_id 
from transactions 
left outer join branch_order_relation on transactions.transaction_id = branch_order_relation.order_id
 where branch_order_relation.order_id is null

And then join other tables, and add your where clauses

Please check my new query. I have posted it as answer.It seems to be working. Please tell if any error is there — user3681970, May 08 '15 at 09:28

SQL Not Exists query

2 Answers2