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
manubatham20
Messages: 562
Registered: September 2010
Location: Seattle, WA, USA
Senior Member

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 #683215 is a reply to message #683214] Tue, 15 December 2020 13:42 Go to previous messageGo to next message
Michel Cadot
Messages: 67850
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I don't understand your output.
For instance, explain the line 2.
There is no (A,1b) in tblB and your output shows it.

Re: Mix of Cross and Full Outer Join [message #683216 is a reply to message #683215] Tue, 15 December 2020 14:51 Go to previous messageGo to next message
manubatham20
Messages: 562
Registered: September 2010
Location: Seattle, WA, USA
Senior Member

My bad, manual error, line/row 2, TblB.Product_Id should be 1ab.

[Updated on: Tue, 15 December 2020 15:16]

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 messageGo to next message
Michel Cadot
Messages: 67850
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
Re: Mix of Cross and Full Outer Join [message #683219 is a reply to message #683218] Wed, 16 December 2020 01:16 Go to previous messageGo to next message
manubatham20
Messages: 562
Registered: September 2010
Location: Seattle, WA, USA
Senior Member

tblA partition by (cust_id)
Thanks a lot Michel!! Is there a name for this feature i.e. explode, iterator?

Manu
Re: Mix of Cross and Full Outer Join [message #683221 is a reply to message #683219] Wed, 16 December 2020 02:02 Go to previous message
Michel Cadot
Messages: 67850
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

It is named partitioned outer join.

Previous Topic: tab_to_string for longer texts ORA-06502-numeric or value error string
Next Topic: Insufficient privileges?
Goto Forum:
  


Current Time: Sat May 15 08:43:33 CDT 2021