Forum Stats

  • 3,734,031 Users
  • 2,246,861 Discussions
  • 7,857,002 Comments

Discussions

Query solution needed

Bolev
Bolev Member Posts: 566
edited November 2010 in SQL & PL/SQL
from the following tables:
Table 1 (ord)

ord_id	prod_id
101		1
101		2
102      	1
102      	3
103      	1
103		2
103		3
104      	1


Table  2 (map)

pkg_id	prod_id
1	1
1	2
2       1
2	3
3	2
3       3
4       1
4       2
4       3

Output

ord_id	pkg_id
101		1
102      	2
103      	4
104
Thanks.

Answers

  • Tubby
    Tubby Member Posts: 6,987 Bronze Crown
    Next time ... why not post the creates for us?
    with 
      ord (ord_id, prod_id) as
    (
      select 101, 1 from dual union all
      select 101, 2 from dual union all
      select 102, 1 from dual union all
      select 102, 3 from dual union all
      select 103, 1 from dual union all
      select 103, 2 from dual union all
      select 103, 3 from dual union all
      select 104, 1 from dual 
    ),
      map (pkg_id, prod_id) as
    (
      select 1 ,1 from dual union all
      select 1 ,2 from dual union all
      select 2 ,    1 from dual union all
      select 2 ,3 from dual union all
      select 3 ,2 from dual union all
      select 3 ,    3 from dual union all
      select 4 ,    1 from dual union all
      select 4 ,    2 from dual union all
      select 4 ,    3 from dual 
    )
    select 
      distinct
        o.ord_id, 
        m.pkg_id
    from
    (
      select 
        ord_id, 
        sum(prod_id) over (partition by ord_id) as ord_sum
      from ord
    ) o,
    (
      select
        pkg_id,
        sum(prod_id) over (partition by pkg_id) as map_sum
      from map
    ) m
    WHERE o.ord_sum = m.map_sum (+)
    order by ord_id asc
    
    TUBBY_TUBBZ?/
    
        ORD_ID     PKG_ID
    ---------- ----------
           101	    1
           102	    2
           103	    4
           104
    
    4 rows selected.
    
    TUBBY_TUBBZ?
    Tubby
  • Bolev
    Bolev Member Posts: 566
    Thanks
    Suppose we have prod_id 1, 2, 3, 4, 5
    and one pkg will be 1+5 and another 2+4
    sum (1+5) same as (2+4)
  • Tubby
    Tubby Member Posts: 6,987 Bronze Crown
    with 
      ord (ord_id, prod_id) as
    (
      select 101, 1 from dual union all
      select 101, 2 from dual union all
      select 102, 1 from dual union all
      select 102, 3 from dual union all
      select 103, 1 from dual union all
      select 103, 2 from dual union all
      select 103, 3 from dual union all
      select 104, 1 from dual 
    ),
      map (pkg_id, prod_id) as
    (
      select 1 ,1 from dual union all
      select 1 ,2 from dual union all
      select 2 ,    1 from dual union all
      select 2 ,3 from dual union all
      select 3 ,2 from dual union all
      select 3 ,    3 from dual union all
      select 4 ,    1 from dual union all
      select 4 ,    2 from dual union all
      select 4 ,    3 from dual 
    )
    select 
      distinct
        o.ord_id, 
        m.pkg_id
    from
    (
      select 
        ord_id, 
        listagg(prod_id, ';') within group (order by prod_id) as ord_junk
      from ord
      group by ord_id
    ) o,
    (
      select
        pkg_id,
        listagg(prod_id, ';') within group (order by prod_id) as map_junk
      from map
      group by pkg_id
    ) m
    WHERE o.ord_junk = m.map_junk (+)
    order by ord_id asc
     46  /
    
        ORD_ID     PKG_ID
    ---------- ----------
           101	    1
           102	    2
           103	    4
           104
    
    4 rows selected.
    
    TUBBY_TUBBZ?
    If you're not on version 11, then replace LISTAGG with your mostest favoritest string aggregation technique.
    Tubby
  • Bolev
    Bolev Member Posts: 566
    Sorry I did not show Oracle version
    For 10g I probably have to use XMLAGG
  • Tubby
    Tubby Member Posts: 6,987 Bronze Crown
    http://www.oracle-base.com/articles/misc/StringAggregationTechniques.php

    Would be a place to start.
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    edited November 2010
    I like Analysis function and math B-)

    I have used below math logic.
    *There are 2 sets.
    *And count of both are same.
    *And latest 1 set contains the other.
    *Then these 2 sets are same.

    http://en.wikipedia.org/wiki/Set_(mathematics)
    with ord(ord_id, prod_id) as(
    select 101,1 from dual union all
    select 101,2 from dual union all
    select 102,1 from dual union all
    select 102,3 from dual union all
    select 103,1 from dual union all
    select 103,2 from dual union all
    select 103,3 from dual union all
    select 104,1 from dual),
    map(pkg_id, prod_id) as(
    select 1,1 from dual union all
    select 1,2 from dual union all
    select 2,1 from dual union all
    select 2,3 from dual union all
    select 3,2 from dual union all
    select 3,3 from dual union all
    select 4,1 from dual union all
    select 4,2 from dual union all
    select 4,3 from dual)
    select ord_id,max(pkg_id) as pkg_id
    from (select a.ord_id,
          decode(a.cnt,count(*),b.pkg_id) as pkg_id
          from (select ord_id, prod_id,
                count(*) over(partition by ord_id) as cnt
                from ord) a
               Left Join 
               (select pkg_id, prod_id,
                count(*) over(partition by pkg_id) as cnt
                from map) b
            on a.cnt=b.cnt
           and a.prod_id=b.prod_id
          group by a.ord_id,a.cnt,b.pkg_id)
    group by ord_id
    order by ord_id;
    
    ORD_ID  PKG_ID
    ------  ------
       101       1
       102       2
       103       4
       104    null
    My SQL articles of OTN-Japan :-)
    http://www.oracle.com/technology/global/jp/pub/jp/ace/sql_image/1/otnj-sql-image1.html
    http://www.oracle.com/technology/global/jp/pub/jp/ace/sql_image/2/otnj-sql-image2.html
    Aketi Jyuuzou
  • Bolev
    Bolev Member Posts: 566
    I already done using XML which is more convinient in my case, but this approch is interesting to try as well.
    Thanks.
This discussion has been closed.