filtering for records based on select criteria [message #684069] |
Wed, 31 March 2021 15:22  |
 |
padler
Messages: 1 Registered: March 2021
|
Junior Member |
|
|

Hi,
I'm looking to build a sql query that will filter for IDs where the IDs are in multiple rows each with different emails and email source codes and want to test whether any of the email source codes match a certain value and not include those in the output dataset. I have attached a screenshot and pasted the dataset below.
For example, say column A has ID "111" and has 3 rows of data. Each row has a different email address and a different email source code. The first row has email1 and email source code of "Active", the second row has email2 and email source code of "Inactive", and the 3rd row has no email or email source code. In this case, I want to test if ID 111 has any email source code of "Active" and exclude the ID in the output dataset because it includes the value of "Active". In this case, this should be excluded from the output because one of the rows has "Active" as the email source code
Alternatively, let's say there is another ID of "222" with 3 rows. The first row has email3 and email source code of "Inactive", the second row has email4 and email source code of "Inactive", and the third row has no email or email source code. In this case, because none of the rows for ID of "222" has a value of "Active", I want to INCLUDE it in my output dataset.
Thanks,
Pierre

Raw Data Set ID EMAIL email source code Desired Output ID
111 email1 Active 222
111 email2 Inactive
111
222 email3 Inactive
222 email4 Inactive
222
-
Attachment: Capture1.PNG
(Size: 21.10KB, Downloaded 381 times)
|
|
|
|
Re: filtering for records based on select criteria [message #684075 is a reply to message #684069] |
Thu, 01 April 2021 09:23  |
John Watson
Messages: 8804 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Without the test case, I can't help much. However, the way I would approach it would be to use a compound query. The first subquery would select all the IDs that are not active, then MINUS a subquery that selects all the IDs that are active. Aggregate to remove the duplicates.
Have a go! There are probably more sophisticated solutions.
--update: come to think of it, you won't need to aggregate. The minus will already have removed duplicates.
[Updated on: Thu, 01 April 2021 09:27] Report message to a moderator
|
|
|