ORACLE SQL select name from the same table using ID provided in another field-CodePudding

How it looks now

id	name	partner_id
1	name1	2
2	name2	3
3	name3	1

How it should look

id	name	partner_id
1	name1	name2
2	name2	name3
3	name3	name1

I tried using joins, but this is not how it's supposed to work. My query was:

select id, 
       name, 
       (select name from table where partner_id = id) 
from   table

But as I already got "where partner_id = id" is not how I thought it will work with the situation where I need to get id by the another column from the same table.

CodePudding user response：

You can use a hierarchical query (then you do not need a JOIN or a correlated sub-query):

SELECT id,
       name,
       PRIOR name AS partner_id
FROM   table_name
WHERE  LEVEL = 2
OR     (LEVEL = 1 AND CONNECT_BY_ISLEAF = 1)
CONNECT BY
       PRIOR id = partner_id
AND    LEVEL <= 2;

If you want to fix your query then you need to give the tables in the inner and outer query aliases and then refer to the columns you are using to correlate between the inner and outer query using those aliases (otherwise the inner query will assume that all the columns are from the local scope of the inner query):

select id, 
       name, 
       (select name from table_name p where t.partner_id = p.id) AS partner_id
from   table_name t

Or, with a JOIN:

select t.id, 
       t.name, 
       p.name AS partner_id
from   table_name t
       LEFT OUTER JOIN table_name p
       ON t.partner_id = p.id

Which, for the sample data:

CREATE TABLE table_name (id, name, partner_id) AS
SELECT 1, 'name1', 2 FROM DUAL UNION ALL
SELECT 2, 'name2', 3 FROM DUAL UNION ALL
SELECT 3, 'name3', 1 FROM DUAL;

All output:

ID NAME PARTNER_ID

2 name2 name3

3 name3 name1

1 name1 name2

ID	NAME	PARTNER_ID
2	name2	name3
3	name3	name1
1	name1	name2

db<>fiddle here

CodePudding user response：


create table dept(
  deptno number(2,0),
  dname  varchar2(14),
  loc    varchar2(13),
  constraint pk_dept primary key (deptno)
);

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)
);

insert into dept
values(10, 'ACCOUNTING', 'NEW YORK');
insert into dept
values(20, 'RESEARCH', 'DALLAS');
insert into dept
values(30, 'SALES', 'CHICAGO');
insert into dept
values(40, 'OPERATIONS', 'BOSTON');
 
insert into emp
values(
 7839, 'KING', 'PRESIDENT', null,
 to_date('17-11-1981','dd-mm-yyyy'),
 5000, null, 10
);
insert into emp
values(
 7698, 'BLAKE', 'MANAGER', 7839,
 to_date('1-5-1981','dd-mm-yyyy'),
 2850, null, 30
);
insert into emp
values(
 7782, 'CLARK', 'MANAGER', 7839,
 to_date('9-6-1981','dd-mm-yyyy'),
 2450, null, 10
);
insert into emp
values(
 7566, 'JONES', 'MANAGER', 7839,
 to_date('2-4-1981','dd-mm-yyyy'),
 2975, null, 20
);
insert into emp
values(
 7788, 'SCOTT', 'ANALYST', 7566,
 to_date('13-JUL-87','dd-mm-rr') - 85,
 3000, null, 20
);
insert into emp
values(
 7902, 'FORD', 'ANALYST', 7566,
 to_date('3-12-1981','dd-mm-yyyy'),
 3000, null, 20
);
insert into emp
values(
 7369, 'SMITH', 'CLERK', 7902,
 to_date('17-12-1980','dd-mm-yyyy'),
 800, null, 20
);
insert into emp
values(
 7499, 'ALLEN', 'SALESMAN', 7698,
 to_date('20-2-1981','dd-mm-yyyy'),
 1600, 300, 30
);
insert into emp
values(
 7521, 'WARD', 'SALESMAN', 7698,
 to_date('22-2-1981','dd-mm-yyyy'),
 1250, 500, 30
);
insert into emp
values(
 7654, 'MARTIN', 'SALESMAN', 7698,
 to_date('28-9-1981','dd-mm-yyyy'),
 1250, 1400, 30
);
insert into emp
values(
 7844, 'TURNER', 'SALESMAN', 7698,
 to_date('8-9-1981','dd-mm-yyyy'),
 1500, 0, 30
);
insert into emp
values(
 7876, 'ADAMS', 'CLERK', 7788,
 to_date('13-JUL-87', 'dd-mm-rr') - 51,
 1100, null, 20
);
insert into emp
values(
 7900, 'JAMES', 'CLERK', 7698,
 to_date('3-12-1981','dd-mm-yyyy'),
 950, null, 30
);
insert into emp
values(
 7934, 'MILLER', 'CLERK', 7782,
 to_date('23-1-1982','dd-mm-yyyy'),
 1300, null, 10
);


SELECT e.ename, e.empno, m.ename as manager, e.mgr
FROM
    emp e, emp m
WHERE e.mgr = m.empno

ENAME    EMPNO    MANAGER    MGR
BLAKE    7698    KING    7839
CLARK    7782    KING    7839
JONES    7566    KING    7839
ALLEN    7499    BLAKE    7698
WARD    7521    BLAKE    7698
MARTIN    7654    BLAKE    7698
TURNER    7844    BLAKE    7698
JAMES    7900    BLAKE    7698
MILLER    7934    CLARK    7782
SCOTT    7788    JONES    7566
FORD    7902    JONES    7566
ADAMS    7876    SCOTT    7788
SMITH    7369    FORD    7902