CODEWITHSUNDEEP
sqloracle-databaseselect
user906153
user906153

Reputation: 1218

Selecting IDs where a string does not exist in any of its data tables

First off, let me start off with the tables I am using. They look something like this:

Table - Transaction
===================
trans_id
name

Table - Checkpoint
==================
trans_id
checkpoint_id
checkpoint_data

A transaction can have multiple checkpoints, and a checkpoint can have only one transaction.

I am having trouble forming a SQL statement that will select the trans_id of all of the transactions of some name that does NOT contain some string in checkpoint_data in any of its associated checkpoints.

What would this SQL statement look like? I am using Oracle, but any SQL should point me in the right direction

Upvotes: 0

Views: 106

Answers (2)

Redian
Redian

Reputation: 334

Select distinct(ch.trans_id) from checkpoint ch join transaction t on ch.trans_id=t.trans_id and t.name like '%test%'  and ch.chech_point not like '%exclude%'

Upvotes: 0

juergen d
juergen d

Reputation: 204864

select trans_id
from transactions t
left outer join checkpoint c on c.trans_id = t.trans_id
where t.name = 'transaction name'
group by t.trans_id
having sum(case when contains(checkpoint_data, 'some string') > 0 
                then 1 
                else 0 
           end) = 0

Upvotes: 1

Related Questions