Query solution needed

Bolev

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.

Tubby

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?

Bolev

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

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.

Bolev

Sorry I did not show Oracle version
For 10g I probably have to use XMLAGG

Tubby

http://www.oracle-base.com/articles/misc/StringAggregationTechniques.php

Would be a place to start.

Aketi Jyuuzou

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

Bolev

I already done using XML which is more convinient in my case, but this approch is interesting to try as well.
Thanks.