Recursive sql problem

Question

I have a problem that I would like have solved via a SQL query. This is going to be used as a PoC (proof of concept).

The problem:

Product offerings are made up of one or many product instances, a product instance can belong to many product offerings. This can be realised like this in a table:

PO | PI

-----

A | 10

A | 11

A | 12

B | 10

B | 11

C | 13

Now I would like to get back the product offer from a set of product instances. E.g. if we send in 10,11,13 the expected result back is B & C, and if we send in only 10 then the result should be NULL since no product offering is made up of only 10. Sending in 10,11,12 would result in A (not A & B since 12 is not a valid product offer in it self).

Prerequisites: The combination of product instances sent in can only result in one specific combination of product offerings, so there is only one solution to each query.

Answer 1

Okay, I think I have it. This meets the constraints you provided. There might be a way to simplify this further, but it ate my brain a little:

select distinct PO 
from POPI x 
where 
  PO not in (
    select PO 
    from POPI 
    where PI not in (10,11,12)
  ) 
  and PI not in (
    select PI 
    from POPI 
    where PO != x.PO 
      and PO not in (
        select PO 
        from POPI 
        where PI not in (10,11,12)
      )
  );

This yields only results who fill the given set which are disjoint with all other results, which I think is what you were asking for. For the test examples given:

• Providing 10,11,12 yields A
• Providing 10,11,13 yields B,C

Answer 2

Edit: Whilst I think mine works fine, Adam's answer is without a doubt more elegant and more efficient - I'll just leave mine here for posterity!

Apologies since I know this has been tagged as an Oracle issue since I started playing. This is some SQL2008 code which I think works for all the stated cases....

declare @test table
(
    [PI] int
)
insert @test values (10), (11), (13)

declare @testCount int
select @testCount = COUNT(*) from @test

;with PO_WITH_COUNTS as 
(
        select  PO_FULL.PO, COUNT(PO_FULL.[PI]) PI_Count
        from    ProductOffering PO_FULL
        left
        join    (
                select  PO_QUALIFYING.PO, PO_QUALIFYING.[PI]
                from    ProductOffering PO_QUALIFYING
                where   PO_QUALIFYING.[PI] in (select [PI] from @test)
                ) AS QUALIFYING
                on      QUALIFYING.PO = PO_FULL.PO
                and     QUALIFYING.[PI] = PO_FULL.[PI]
        group by
                PO_FULL.PO
        having  COUNT(PO_FULL.[PI]) = COUNT(QUALIFYING.[PI])
)
select  PO_OUTER.PO
from    PO_WITH_COUNTS PO_OUTER 
cross 
join    PO_WITH_COUNTS PO_INNER
where   PO_OUTER.PI_Count = @testCount
or      PO_OUTER.PO <> PO_INNER.PO
group by
        PO_OUTER.PO, PO_OUTER.PI_Count
having  PO_OUTER.PI_Count = @testCount 
or      PO_OUTER.PI_Count + SUM(PO_INNER.PI_Count) = @testCount

Not sure if Oracle has CTEs but could just state the inner query as two derived tables. The cross join in the outer query lets us find combinations of offerings that have all the valid items. I know that this will only work based on the statement in the question that the data is such that there is only 1 valid combination for each requested set, Without that it's even more complicated as counts are not enough to remove combinations that have duplicate products in them.

Answer 3

I don't have a db in front of me, but off the top of my head you want the list of POs that don't have any PIs not in your input list, ie

select distinct po 
from tbl 
where po not in ( select po from tbl where pi not in (10,11,13) )

Edit: Here are the example other cases:
When input PI = 10,11,13 the inner select returns A so the outer select returns B, C
When input PI = 10 the inner select returns A,B,C so the outer select returns no rows
When input PI = 10,11,12 the inner select returns C so the outer select returns A,B

Edit: Adam has pointed out that this last case doesn't meet the requirement of only returning A (that'll teach me for rushing), so this isn't yet working code.