Unique row in oracle sql-CodePudding

I have a "Subscriber" table :

S_ID	con
1	111
2	222
3	333
4	444
5	555

also,I have two tables - "product_subscriber" and "service_subscriber" and prod_name and ser_name have the same name

product_subscriber:

s_id	id	prod_name
1	20	offer 1
1	21	offer 2
2	20	offer 1
3	21	offer 2

service_subscriber:

s_id	id	ser_name
1	10	offer 1
1	11	offer 2
3	11	offer 2
3	10	offer 1
5	10	offer 1

I want to get table like this:

s_id	ser_name	prod_name
1	offer 1	offer 1
1	offer 2	offer 2
2	offer 1
3	offer 2	offer 2
3		offer 1
5		offer 1

Below is the code, but for users who have more than two offers, for example s_id = 1, Incorrectly matched, something like that

s_id	ser_name	prod_name
1	offer 1	offer 2
1	offer 2	offer 2
1	offer 2	offer 1
1	offer 1	offer 1

SELECT p.pid,
       p.mobile,
       o.of_name,
       s.ser_name 
FROM   person p
       LEFT OUTER JOIN offer o
       ON p.pid = o.pid
       LEFT OUTER JOIN service s
       ON p.pid = s.pid
WHERE  o.pid IS NOT NULL
OR     s.pid IS NOT NULL

CodePudding user response：

You can use:

SELECT s.*,
       ps.prod_name,
       ss.ser_name
FROM   subscriber s
       LEFT OUTER JOIN product_subscriber ps
       ON s.s_id = ps.s_id
       LEFT OUTER JOIN service_subscriber ss
       ON (   s.s_id = ss.s_id
          AND (ps.s_id IS NULL OR ps.id = ss.id   10))
WHERE  ps.s_id IS NOT NULL
OR     ss.s_id IS NOT NULL

Which, for the sample data:

CREATE TABLE subscriber (S_ID, con) AS
SELECT 1, 111 FROM DUAL UNION ALL
SELECT 2, 222 FROM DUAL UNION ALL
SELECT 3, 333 FROM DUAL UNION ALL
SELECT 4, 444 FROM DUAL UNION ALL
SELECT 5, 555 FROM DUAL;

CREATE TABLE product_subscriber (s_id, id, prod_name) AS
SELECT 1, 20, 'offer 1' FROM DUAL UNION ALL
SELECT 1, 21, 'offer 2' FROM DUAL UNION ALL
SELECT 2, 20, 'offer 1' FROM DUAL UNION ALL
SELECT 3, 21, 'offer 2' FROM DUAL;

CREATE TABLE service_subscriber (s_id, id, ser_name) AS
SELECT 1, 10, 'offer 1' FROM DUAL UNION ALL
SELECT 1, 11, 'offer 2' FROM DUAL UNION ALL
SELECT 3, 11, 'offer 2' FROM DUAL UNION ALL
SELECT 3, 10, 'offer 1' FROM DUAL UNION ALL
SELECT 5, 10, 'offer 1' FROM DUAL;

Outputs:

S_ID CON PROD_NAME SER_NAME

1 111 offer 1 offer 1

1 111 offer 2 offer 2

2 222 offer 1 null

3 333 offer 2 offer 2

5 555 null offer 1

S_ID	CON	PROD_NAME	SER_NAME
1	111	offer 1	offer 1
1	111	offer 2	offer 2
2	222	offer 1	null
3	333	offer 2	offer 2
5	555	null	offer 1

db<>fiddle here

CodePudding user response：

To me, it looks as if you need to include additional join condition - a "virtual" column created by row_number analytic function (at least, that's what I figured out looking at desired output); something like this:

Sample data:

SQL> with
  2  subscriber (s_id, con) as
  3    (select 1, 111 from dual union all
  4     select 2, 222 from dual union all
  5     select 3, 333 from dual union all
  6     select 4, 444 from dual union all
  7     select 5, 555 from dual
  8    ),
  9  product_subscriber (s_id, id, prod_name) as
 10    (select 1, 20, 'offer 1' from dual union all
 11     select 1, 21, 'offer 2' from dual union all
 12     select 2, 20, 'offer 1' from dual union all
 13     select 3, 21, 'offer 2' from dual
 14    ),
 15  service_subscriber (s_id, id, ser_name) as
 16    (select 1, 10, 'offer 1' from dual union all
 17     select 1, 11, 'offer 2' from dual union all
 18     select 3, 11, 'offer 2' from dual union all
 19     select 3, 10, 'offer 1' from dual union all
 20     select 5, 10, 'offer 1' from dual
 21    ),
 22  --

Query begins here; CTEs just calculate row numbers in an outer join of subscriber and another table:

 23  temp_ps as
 24    (select s.s_id, ps.id, ps.prod_name,
 25            row_number() over (partition by s.s_id order by ps.id desc) rn
 26     from subscriber s left join product_subscriber ps on ps.s_id = s.s_id
 27    ),
 28  temp_ss as
 29    (select s.s_id, ss.id, ss.ser_name,
 30            row_number() over (partition by s.s_id order by ss.id desc) rn
 31     from subscriber s left join service_subscriber ss on ss.s_id = s.s_id
 32    )
 33  select ss.s_id, ss.ser_name, ps.prod_name
 34  from temp_ss ss left join temp_ps ps on ps.s_id = ss.s_id and nvl(ps.rn, -1) = nvL(ss.rn, -1)
 35  order by ss.s_id, ss.ser_name, ps.prod_name;

      S_ID SER_NAME   PROD_NAME
---------- ---------- ----------
         1 offer 1    offer 1
         1 offer 2    offer 2
         2            offer 1
         3 offer 1
         3 offer 2    offer 2
         4
         5 offer 1

7 rows selected.

SQL>