Home » SQL & PL/SQL » SQL & PL/SQL » Mix of Cross and Full Outer Join (https://livesql.oracle.com/apex)
Mix of Cross and Full Outer Join [message #683214] |
Tue, 15 December 2020 12:20 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
manubatham20
Messages: 566 Registered: September 2010 Location: Seattle, WA, USA
|
Senior Member |
![manubatham2006@gmail.com](/forum/theme/orafaq/images/google.png)
|
|
Hello,
Could you please help formulating query for below data and use case.
For every cust_id, and brand_name from TblA, all the product_id should be there from TblB. If it matches between 2 datasets, then match it otherwise just create another row where product_id will only be coming from TblB.
WITH
tblA AS
(
select 1 cust_id, 'A' brand_name, '1a' product_id, 1 val from dual union all
select 1 cust_id, 'A' brand_name, '1ab' product_id, 2 val from dual union all
select 1 cust_id, 'B' brand_name, '1b' product_id, 3 val from dual union all
select 2 cust_id, 'A' brand_name, '2a' product_id, 4 val from dual union all
select 3 cust_id, 'A' brand_name, NULL product_id, NULL val from dual union all
select 3 cust_id, 'C' brand_name, NULL product_id, NULL val from dual
),
tblB AS
(
select 'A' brand_name,'1a' product_id from dual union all
select 'A' brand_name,'2a' product_id from dual union all
select 'A' brand_name,'1ab' product_id from dual union all
select 'A' brand_name,'3a' product_id from dual union all
select 'B' brand_name,'1b' product_id from dual union all
select 'B' brand_name,'2b' product_id from dual union all
select 'C' brand_name,'3c' product_id from dual
)
SELECT
*
FROM
tblA
JOIN
tblB
ON
Thanks,
Manu
[Updated on: Tue, 15 December 2020 12:30] Report message to a moderator
|
|
|
|
|
Re: Mix of Cross and Full Outer Join [message #683218 is a reply to message #683216] |
Wed, 16 December 2020 00:29 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68658 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
One way:
SQL> WITH
2 tblA AS
3 (
4 select 1 cust_id, 'A' brand_name, '1a' product_id, 1 val from dual union all
5 select 1 cust_id, 'A' brand_name, '1ab' product_id, 2 val from dual union all
6 select 1 cust_id, 'B' brand_name, '1b' product_id, 3 val from dual union all
7 select 2 cust_id, 'A' brand_name, '2a' product_id, 4 val from dual union all
8 select 3 cust_id, 'A' brand_name, NULL product_id, NULL val from dual union all
9 select 3 cust_id, 'C' brand_name, NULL product_id, NULL val from dual
10 ),
11 tblB AS
12 (
13 select 'A' brand_name,'1a' product_id from dual union all
14 select 'A' brand_name,'2a' product_id from dual union all
15 select 'A' brand_name,'1ab' product_id from dual union all
16 select 'A' brand_name,'3a' product_id from dual union all
17 select 'B' brand_name,'1b' product_id from dual union all
18 select 'B' brand_name,'2b' product_id from dual union all
19 select 'C' brand_name,'3c' product_id from dual
20 )
21 SELECT *
22 FROM
23 tblA partition by (cust_id)
24 right outer join
25 tblB
26 ON tbla.brand_name = tblb.brand_name and tbla.product_id = tblb.product_id
27 WHERE tblb.brand_name in
28 (select brand_name from tbla a where a.cust_id = tbla.cust_id)
29 order by 1,5,6
30 /
CUST_ID B PRO VAL B PRO
---------- - --- ---------- - ---
1 A 1a 1 A 1a
1 A 1ab 2 A 1ab
1 A 2a
1 A 3a
1 B 1b 3 B 1b
1 B 2b
2 A 1a
2 A 1ab
2 A 2a 4 A 2a
2 A 3a
3 A 1a
3 A 1ab
3 A 2a
3 A 3a
3 C 3c
|
|
|
|
|
Goto Forum:
Current Time: Mon Jun 03 09:19:10 CDT 2024
|