StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p><strong>Edit:</strong> 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!</p> <p>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....</p> <pre><code>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 </code></pre> <p>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.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload