I want to create a view that present only the results and not present the duplicates, I have 3 tables in oracle database:
The first table contain general information about a person
+-----------+-------+-------------+
| ID | Name | Birtday_date|
+-----------+-------+-------------+
| 1 | Byron | 12/10/1998 |
| 2 | Peter | 01/11/1973 |
| 4 | Jose | 05/02/2008 |
+-----------+-------+-------------+
The second table contain information about a telephone of the people in the first table.
+-------+----------+----------+----------+
| ID |ID_Person |CELL_TYPE | NUMBER |
+-------+- --------+----------+----------+
| 1221 | 1 | 3 | 099141021|
| 2221 | 1 | 2 | 099091925|
| 3222 | 1 | 1 | 098041013|
| 4321 | 2 | 1 | 088043153|
| 4561 | 2 | 2 | 090044313|
| 5678 | 4 | 1 | 092049013|
| 8990 | 4 | 2 | 098090233|
+----- -+----------+----------+----------+
The Third table contain information about a email of the people in the first table.
+------+----------+----------+---------------+
| ID |ID_Person |MAIL_TYPE | Email |
+------+- --------+----------+---------------+
| 221 | 1 | 1 |[email protected] |
| 222 | 1 | 2 |[email protected] |
| 421 | 2 | 1 |[email protected] |
| 451 | 2 | 2 |[email protected]|
| 578 | 4 | 1 |[email protected]|
| 899 | 4 | 2 |[email protected] |
+------+----------+----------+---------------+
if i do a inner join with this tables the result will do something like that
+-----+-------+-------------+----------+----------+----------+----------------+
| ID | Name | Birtday_date| CELL_TYPE| NUMBER |MAIL_TYPE|Email |
+-----+-------+-------------+----------+----------+----------+----------------+
| 1 | Byron | 12/10/1998 | 3 | 099141021|1 |[email protected] |
| 1 | Byron | 12/10/1998 | 3 | 099141021|2 |[email protected] |
| 1 | Byron | 12/10/1998 | 2 | 099091925|1 |[email protected] |
| 1 | Byron | 12/10/1998 | 2 | 099091925|2 |[email protected] |
| 1 | Byron | 12/10/1998 | 1 | 098041013|1 |[email protected] |
| 1 | Byron | 12/10/1998 | 1 | 098041013|2 |[email protected] |
| 2 | Peter | 01/11/1973 | 1 | 088043153|1 |[email protected] |
| 2 | Peter | 01/11/1973 | 1 | 088043153|2 |[email protected] |
| 2 | Peter | 01/11/1973 | 2 | 090044313|1 |[email protected] |
| 2 | Peter | 01/11/1973 | 2 | 090044313|2 |[email protected] |
| 4 | Jose | 05/02/2008 | 1 | 088043153|1 |[email protected] |
| 4 | Jose | 05/02/2008 | 1 | 088043153|2 |[email protected] |
| 4 | Jose | 05/02/2008 | 2 | 088043153|1 |[email protected] |
| 4 | Jose | 05/02/2008 | 2 | 088043153|2 |[email protected] |
+-----+-------+-------------+----------+----------+----------+----------------+
So the result that i will to present in a view is the next
+-----+-------+-------------+----------+----------+----------+----------------+
| ID | Name | Birtday_date| CELL_TYPE| NUMBER |MAIL_TYPE|Email |
+-----+-------+-------------+----------+----------+----------+----------------+
| 1 | Byron | 12/10/1998 | 3 | 099141021|1 |[email protected] |
| 1 | Byron | 12/10/1998 | | |2 |[email protected] |
| 1 | Byron | 12/10/1998 | 2 | 099091925| | |
| 1 | Byron | 12/10/1998 | 1 | 098041013| | |
| 2 | Peter | 01/11/1973 | 1 | 088043153|1 |[email protected] |
| 2 | Peter | 01/11/1973 | | |2 |[email protected] |
| 2 | Peter | 01/11/1973 | 2 | 090044313| | |
| 4 | Jose | 05/02/2008 | 1 | 092049013|1 |[email protected] |
| 4 | Jose | 05/02/2008 | | |2 |[email protected] |
| 4 | Jose | 05/02/2008 | 2 | 098090233| | |
+-----+-------+-------------+----------+----------+----------+----------------+
I tried to achieve a similar output using
case
when row_number() over (partition by table1.id order by table2.type) = 1
then table1.value
end
as "VALUE"
But the result is nothing that I expect and some rows they repeats
Hope this helps.
Create table person(ID int ,Name varchar(20), Birtday_date date)
Insert into person values
(1,'Byron' ,'12/10/1998'),
(2,'Peter' ,'01/11/1973'),
(4,'Jose ' ,'05/02/2008')
Create table phones (ID int,ID_Person int,CELL_TYPE int,NUMBER float)
Insert into phones values
(1221, 1 , 3,099141021),
(2221, 1 , 2,099091925),
(3222, 1 , 1,098041013),
(4321, 2 , 1,088043153),
(4561, 2 , 2,090044313),
(5678, 4 , 1,092049013),
(8990, 4 , 2,098090233)
Create table emails(ID int,ID_Person int, MAIL_TYPE int, Email varchar(100))
Insert into emails values
(221, 1 , 1, '[email protected] '),
(222, 1 , 2, '[email protected] '),
(421, 2 , 1, '[email protected] '),
(451, 2 , 2, '[email protected]'),
(578, 4 , 1, '[email protected]'),
(899, 4 , 2, '[email protected] ')
select p.id, p.name, p.Birtday_date,
case when Lag(number) over(partition by p.id order by p.id,pe.id) = number then null else cell_type end as cell_type,
case when Lag(number) over(partition by p.id order by p.id,pe.id) = number then null else number end as number,
mail_type as mail_type, email as email
from person p left join
(select pp.ID_Person, cell_type, number, mail_type, email,pp.id from
(select ID_Person, cell_type, number,id,
row_number() over (partition by ID_Person order by id ) as seqnum
from phones
) pp left join
(select ID_Person,
mail_type, email, 1 as seqnum
from emails
)e on pp.ID_Person = e.ID_Person and pp.seqnum = e.seqnum
) pe
on pe.ID_Person = p.Id
order by p.id, pe.id