Oracle 父子关系查询

嗨，

我有两张表。

1.联系人
2.接触点

联系人表包含row_id，名字，姓氏，dob等Row_id是主键。

Contact_point表包含电话号码、类型和fk到联系人表。

我不希望子表中的每个记录重复父信息。只有第一个记录需要有父信息。

联系表数据样本

1约翰·史密斯30/2/2009
2阿德尔·卡迈勒12/3/2001

样品接触点表
p.k型电话联系人 _ 标识
123移动088654387 1
231工作66545298 1
342移动456625 2
657工作8897642 2

当我加入这两个表时，我需要看到如下结果:

1  john  smith  123  mobile  088654387 (row 1)
--------------------------------------
                231  work     66545298 (row 2)
--------------------------------------
2  adel   kamel 342  mobile   456625   (row 3)
--------------------------------------
                657  work     8897642  (row 4)
----------------------------------------------

-对问题的进一步更新

CREATE TABLE CONTACT_POINT
    (row_id varchar(50),
  type varchar(30), 
 phone_number varchar(100),
 contact_id varchar(50))
;
-- insert into contact
INSERT INTO CONTACT
    (row_id, first_name, last_name)
VALUES
    ('123', 'john', 'Fred');
 
INSERT INTO CONTACT
    (row_id, first_name, last_name)
VALUES
    ('456', 'Adel', 'Kamel');

-- insert into contact_point

INSERT INTO CONTACT_POINT
    (row_id,type,phone_number,contact_id)
VALUES
    ('1', 'Mobile', '078766455', '123');
 
INSERT INTO CONTACT_POINT
    (row_id,type,phone_number,contact_id)
VALUES
    ('2', 'Work', '045651539', '123');

INSERT INTO CONTACT_POINT
    (row_id,type,phone_number,contact_id)
VALUES
    ('3', 'Mobile', '66545290', '456');

INSERT INTO CONTACT_POINT
    (row_id,type,phone_number,contact_id)
VALUES
    ('4', 'Work', '03555344', '456');

听起来你想解开枢轴:

create table CONTACT (
  row_id varchar2(50), first_name varchar2(50), last_name varchar2(50)
);

CREATE TABLE CONTACT_POINT (
row_id varchar2(50),
type varchar2(30),
phone_number varchar2(100),
contact_id varchar2(50)
)
;
-- insert into contact
INSERT INTO CONTACT
(row_id, first_name, last_name)
VALUES
('123', 'john', 'Fred');

INSERT INTO CONTACT
(row_id, first_name, last_name)
VALUES
('456', 'Adel', 'Kamel');

-- insert into contact_point

INSERT INTO CONTACT_POINT
(row_id,type,phone_number,contact_id)
VALUES
('1', 'Mobile', '078766455', '123');

INSERT INTO CONTACT_POINT
(row_id,type,phone_number,contact_id)
VALUES
('2', 'Work', '045651539', '123');

INSERT INTO CONTACT_POINT
(row_id,type,phone_number,contact_id)
VALUES
('3', 'Mobile', '66545290', '456');

INSERT INTO CONTACT_POINT
(row_id,type,phone_number,contact_id)
VALUES
('4', 'Work', '03555344', '456'); 

select contact_id, first_name, last_name, v1, v2
from   contact c
join   contact_point p
on     c.row_id = p.contact_id
unpivot (
  ( v1, v2 ) for ( c1, c2 ) in ( ( type, phone_number ) )
);

FIRST_NAME    LAST_NAME    CONTACT_ID    V1        V2          
john          Fred         123           Mobile    078766455    
john          Fred         123           Work      045651539    
Adel          Kamel        456           Mobile    66545290     
Adel          Kamel        456           Work      03555344    

你可以在https://blogs.oracle.com/sql/how-to-convert-rows-to-columns-and-back-again-with-sql-aka-pivot-and-unpivot#unpivot