CODEWITHSUNDEEP
sqloracle-database
Philip St
Philip St

Reputation: 89

SQL: Select IDs which are not mapped to values in another column

I have table with two columns like this:

ROLE_ID      RESTRICTION_ID
---------    ---------------
2            null
2            15
2            null
13           12
13           null
13           13
555          null
555          null
555          null
91           10
91           12
91           null

I need to get: 

    ROLE_ID      RESTRICTION_ID
    ---------    ---------------
    555          null
    555          null
    555          null

Meaning , that i need all ROLE_ID's which are not connected to any RESTRICTION_ID.

If there is some number in the RESTRICTION_ID column for some ROLE_ID is want the ROLE_ID excluded from the select statement results.

Upvotes: 0

Views: 230

Answers (8)

Serg
Serg

Reputation: 22811

Count them all

with t as (
    -- sample data
    select 2 as ROLE_ID  ,null as RESTRICTION_ID from dual union all
    select 2    ,15   from dual union all
    select 2    ,null from dual union all
    select 13   ,12   from dual union all
    select 13   ,null from dual union all
    select 13   ,13   from dual union all
    select 555  ,null from dual union all
    select 555  ,null from dual union all
    select 555  ,null from dual union all
    select 91   ,10   from dual union all
    select 91   ,12   from dual union all
    select 91   ,null from dual 
)
select ROLE_ID,RESTRICTION_ID
from (
    select t.* 
        ,count(RESTRICTION_ID) over(partition by ROLE_ID)  as cnt
    from t
    ) x
where cnt=0;

Upvotes: 0

Robert Columbia
Robert Columbia

Reputation: 6418

SELECT ROLE_ID, RESTRICTION_ID
FROM Table
WHERE
ROLE_ID NOT IN (SELECT ROLE_ID FROM Table WHERE RESTRICTION_ID IS NOT NULL)

Upvotes: 0

Matt
Matt

Reputation: 14361

and becuase you have tagged SQL here is a Window function with common table expression that does the job nicely as well.

WITH cte AS (
    SELECT
       *
       ,COUNT(RESTRICTION_ID) OVER (PARTITION BY ROLE_ID) as CountOfRestrictionsIds
    FROM
       TableName
)

SELECT *
FROM
    cte
WHERE
    CountOfRestrictionsIds = 0

Upvotes: 0

kjmerf
kjmerf

Reputation: 4345

SELECT ROLE_ID, RESTRICTION_ID
FROM <Your Table>
WHERE RESTRICTION_ID IS NULL
AND ROLE_ID NOT IN 
(SELECT ROLE_ID FROM <Your Table> WHERE RESTRICTION_ID IS NOT NULL) sub

Upvotes: 0

Matt
Matt

Reputation: 14361

SELECT t1.*
FROM
    table t1
    LEFT JOIN table t2
    ON t1.ROLE_ID = t2.ROLE_ID
    AND t2.RESTRICTION_ID IS NOT NULL
WHERE
    t2.ROLE_ID IS NULL

Just because everyone else is showing the WHERE IN (SELECT ... answer here is a way to do it via a self join....

And just to show one way it can be done using EXISTS...

SELECT t1.*
FROM
    table t1
WHERE
    NOT EXISTS (SELECT *
             FROM
                table t2
             WHERE
                t1.ROLE_ID = t2.ROLE_ID 
                AND t2.RESTRICTION_ID IS NOT NULL)

Upvotes: 2

Dave
Dave

Reputation: 483

Select role_id, restriction_id
From Table 
Where role_id in (Select ROLE_ID

From Table
Group by ROLE_ID
Having COUNT(RESTRICTION_ID)=0)

Better maybe...

Upvotes: 0

Abecee
Abecee

Reputation: 2393

The following should get you started:

SELECT
  role_id
  , restriction_id
FROM Data
WHERE role_id NOT IN
 (SELECT
    role_id
  FROM Data
  GROUP BY role_id
  HAVING COUNT(restriction_id) > 0
 );

Please comment if and as this requires adjustment / further detail.

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133380

Select the rows where resctriction_id is null

select * from my_table 
where resctiction_id is null
and role_id not in (select role_id from  my_table  where resctiction_id is not null) ;

Upvotes: 2

Related Questions