Alternative for subquery to perform NOT IN condition

I have a scenario, where data gets inserted into a particular event based on different cases.
One set of entry happens with certain fields/columns & the other set of entry happens with different fields/columns. In the end result, we have certain values in common & need to know if there is any unique entry happened for both the cases.

Entry 1:

dataID Column1 Column2
123 alpha Lorem ipsum code
124 beta ipsum code lorem
125 gama code lorem ipsum
126 delta ipsum lorem code

Entry 2:

dataID Column3 Column4
124 mary ipsum lorem
125 siri lorem ipsum
126 alexa ipsum lorem

Now these two cases are under same event, its just that the columns & values are different. What I need to bring out is
Final Data:


This is because, 124, 125, 126 are present in both the cases irrespective of the values present for rest of the columns.
Usual way of approach is,

Select dataID from Event where dataID NOT IN (select dataID from Event where column3 = ‘mary’ OR column3 = ‘siri’ OR column3 = ‘alexa’)

You know, on one hand this is confusing and then on the other hand it’s not exactly straight forward. In a certain way it’s not totally clear.

Select dataID from Event where column3 not in ( ‘mary’ , ‘siri’ , ‘alexa’)

1 Like

If you observe Entry1 & Entry2, only dataID is common rest are different. That is, Entry1 doesn’t have Column3 & 4, similarly Entry2 doesn’t have Column1 & 2.
Now the requirement is, we need to identify dataID’s from Entry1 which are not present in Entry2.
Here the answer is 123.

As per your query, it just checks dataID’s from Entry2 only but not from Entry1 because Entry1 doesn’t have column3.
Select dataID from Event where column3 not in ( ‘mary’ , ‘siri’ , ‘alexa’)

That is the reason, I mentioned below sub-query, to first identify the dataID’s to match from Entry2 & then check with NOT IN to match from entire Event. But sub-queries doesn’t work at NRQL

Select dataID from Event where dataID NOT IN (select dataID from Event where column3 = ‘mary’ OR column3 = ‘siri’ OR column3 = ‘alexa’)

The last bit of NRQL you have there can be restated as what I posted. You suggested that’s what you wanted so I wrote it in a way that would work. Maybe write what you really wanted?

When you talk about “entries” this is confusing because you have EVENTS with the same event type. You also say column when you have attributes. Because you are scrubbing the names and being generic it makes it much harder for people not working with your app to get your problem. We do not know why you are picking 123 based on your data samples.

You may want to consider looking at your use of custom attributes and adjust what the application is adding in order to make your NRQL simply. As you know you cannot join or sub query. You can often fix this by better use of attributes.

1 Like