CODEWITHSUNDEEP
sqloracle-database
Charlie Patton
Charlie Patton

Reputation: 445

Finding users/records that don't exist in subquery

I am writing a query to pull a list of student enrollments, and creating a virtual column with logic to assign students with various academic plans to communication groups (comm_group). The source view I've been provided to work with pulls one record for EACH academic plan for a student with ANY enrollment in our department. As a result, there are records of enrollments that have nothing to do with our department, because OTHER enrollments that in our department exist. I could just filter out those rows, but I would like to double-check the logic in my virtual column by finding any students who have ALL null values in the comm_group column. That would indicate that I missed some plan codes somewhere. Here's some sample data:

USER_ID PLAN_CODE COMM_GROUP
1 EDA Ed Administration
1 CAS NULL
2 EDB Ed Business
2 BUS NULL
3 EDC NULL
3 HIS NULL

User 3 has an enrollment in EDC, so should have a value for that column for COMM_GROUP for that row. This means I have left out EDC from my case statements in my virtual column. I would like to identify all such errors by selecting finding all users who ONLY have NULL values.

I'm almost there, but I'm missing something. My code looks like this right now:

WITH A AS (long query)
SELECT A.USER_ID
FROM A
WHERE A.USER_ID NOT IN (SELECT A.USER_ID FROM A WHERE A.COMM_GROUP IS NOT NULL)

That gives me the user IDs, but really I need to whole row, but I don't want to have to enumerate column from the with statement. I tried SELECT A.* but I get a column is ambiguously defined error.

Is there a way to select all columns from A? I'd also be open to other approaches to the problem.

Upvotes: 1

Views: 92

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270553

If you want users with only NULL values, you can use aggregation:

select user_id
from g
group by user_id
having max(COMM_GROUP) is null;

Upvotes: 2

Sayan Malakshinov
Sayan Malakshinov

Reputation: 8655

It would be easier to use analytic count() for your requirements:

WITH A AS (long query)
SELECT *
FROM (select a.*,count(A.COMM_GROUP) over(partition by a.user_id) cnt from a)
WHERE cnt=0

Upvotes: 1

Related Questions