CODEWITHSUNDEEP
sqlpresto
Chris90
Chris90

Reputation: 1998

SQL query (self-join needed?)

I have a table with multiple fields and two i.d fields, one field is called regular_id and second is called special.

Each row can have a value under regular_id and special_id, if there is a value under special_id then it exists somewhere else in the table as a regular_id.

I want to build a query that says -

Give me all rows where the special_id field is null, but the value in the regular_id field does not exist anywhere else ( in any other row/record) in that table under the special field.

Upvotes: 0

Views: 274

Answers (3)

John Martin
John Martin

Reputation: 31

Sub-query or exist can do.

This is very similar to the famous scott.EMP table in Oracle.

create table emp(  
  empno    number(4,0),  
  ename    varchar2(10),  
  job      varchar2(9),  
  mgr      number(4,0),  
  hiredate date,  
  sal      number(7,2),  
  comm     number(7,2),  
  deptno   number(2,0),  
  constraint pk_emp primary key (empno),  
  constraint fk_deptno foreign key (deptno) references dept (deptno)  
)

So regular_id in your question is the empno (employee ID) in scott.EMP table and special_id is the mgr (manager ID)

Now your question translates to scott.EMP: where mgr field is null, but the value in the empno field does not exist anywhere else (in any other row/record) in that table under the mgr field.

select m.*
from scott.EMP m
where m.mgr IS NULL
and m.empno not in (select mgr from scott.EMP where mgr is not null)

thanks for Thorsten Kettner's correction, always pay attention to NULL in list

Your question translates to natural language: The person who has no manager and is not manager of any employee.

Upvotes: 1

Niharika Bitra
Niharika Bitra

Reputation: 477

A self join is the right way to go.

SELECT a.special_id, a.regular_id
FROM tablename  a
LEFT JOIN tablename b
    ON a.regular_id = b.special_id 
WHERE a.special_id IS NULL
AND b.special_id IS NULL;

Note: Replace tablename with your actual table name.

Sample Data:

REGULAR_ID  SPECIAL_ID
1           1
1           2
2           1
3           1
1           NULL
3           NULL

Result:

REGULAR_ID  SPECIAL_ID
3           NULL

Upvotes: 1

Abra
Abra

Reputation: 20914

NOTE: Untested.

select A.*
from table A
where A.special_id is null
and not exists (select 1 from table B where B.special_id = A.regular_id)

Of-course A and B are aliases for the same database table.

Upvotes: 1

Related Questions