Sql query help

Transact-SQL
1

Hi Guys i dont know if this is possiable but i have a table below

person product sold
a a 10
a b 5
a c 4
b b 67
b e 15
b a 1
b a 1
c a 1
c a 1
c a 1
c a 10

i am trying to create a query that will show me that will show me a list of person that cover all products.
so the answer should be

A
B
can this be done?

2

did you mean a list of person that cover more than one product?
a does not cover e
b does not cover c

3

no the question i am trying to answer is give me a list of person combo which will cover all the producs so if you take A and B combine they cover all the products

4

always provide sample data otherwise you will most probably not get any sort of answer.
like the following.
while I try to figure out a solution

create table #persons(personid int identity(1,1), personNumber char(1))
create table #products(productid int identity(1,1), product char(1))
create table #purchases(personid int, productid int, sold int)

insert into #persons
values('a'),('b'),('c')

insert into #products
values('a'),('b'),('c'),('e')

--a
insert into #purchases
select 1,1,10 union
select 1,2,5 union
select 1,3,4 

--b
insert into #purchases
select 2,2,67 union all
select 2,4,15 union all
select 2,1,1 union all
select 2,1,1

--b
insert into #purchases
select 3,1,1 union all
select 3,1,1 union all
select 3,1,1 union all
select 3,1,10

select pe.personNumber, pr.product, p.sold
  From #persons pe
  join #purchases p on pe.personid = p.personid
  join #products pr on pr.productid = p.productid
  order by 1



drop table #products
drop table #persons
drop table #purchases
5

But - person c also covers product a and should also be included in the combination. How do you know the list of products to be covered? Why wouldn't product d be included and therefore no persons qualify?

6

I'm assuming you want the minimal set of people that would cover all the products. It's a well known Graph Theory problem, but I have to admit it's been a while since I looked into that.

7

There is nothing person C covers that the others done ! I'm a nutshell I want to bring combo showing the least amount of person that cover all products

8

Yes AndyC spot on

9

Try this:

Declare @table as table
(
person char(1),
product char(1),
sold numeric(5,0)



);

insert @table 
values
('a','a','10'),
('a','b','5'),
('a','c','4'),
('b','b','67'),
('b','e','15'),
('b','a','1'),
('b','a','1'),
('c','a','1'),
('c','a','1'),
('c','a','1'),
('c','a','10')


;


with tst
as
(

select distinct a.person, a.product 
from @table a
) 


,tst1
as
(

select distinct  A.person,    b.product ,  b.person  [ref]
from @table b
,tst A
where b.person <> A.person 
and not exists
	(
	select 1
	from @table c
	where c.person = A.person 
	and c.product = b.product 
	)

union

select  a.person  
,a.product
,c.person  [ref]
from tst a 
, tst c
where a.person <> c.person  
and c.product = a.product 
	
union 

select   
a.person 
,a.product 
,b.person 
from tst a
outer apply
	(
	select distinct b.person
	from tst b
	where b.person <> a.person 
	) b(person)

where not exists
	(
	select 1
	from tst c
	where c.person <> a.person 
	and c.product = a.product
	and c.person = b.person 
	)
)

select a.person  
,a.ref [Second Person] 
--,COUNT(distinct a.product) [Count of Products]
from tst1 a
group by  a.person , a.ref 
having COUNT(distinct a.product) = (select COUNT(distinct product) from @table )
10

There is almost certainly a better solution, but I belive the following would work regardless of how many people you have (well up to the point the recursive function explodes!)

drop table if exists #temp
create table #temp
(
	person char(1),
	product char(1),
	sold int
)

insert into #temp
values
('a','a',10),
('a','b',5),
('a','c',4),
('b','b',67),
('b','e',15),
('b','a',1),
('b','a',1),
('c','a',1),
('c','a',1),
('c','a',1),
('c','a',10);
--,
--('d','a',1),
--('d','b',1),
--('d','c',1),
--('d','e',1);

Drop Table If Exists #PeoplePermutations
Create Table #PeoplePermutations
(
	Person Char(1),
	PermutationID Int
)

Go
Create Or Alter Procedure #SubPermutations(@root char(1), @current char(1), @permutation int output)
As
Begin
	If @current Is Not Null
	Begin
		Select @root, @permutation
		Union Select Distinct person, @permutation As permutation From #temp Where person <> @root and person >= @current
	
		Set @permutation += 1
		Select @root, @permutation
		Union Select Distinct person, @permutation As permutation From #temp Where person <> @root and person <= @current
	
		Declare @next char(1) =(Select min(person) From #Temp 
								Where
									Person <> @root  
									And Person > @current)
		Set @permutation += 1
		Exec #SubPermutations @root=@root, @current = @next, @permutation = @permutation Output
	End
End
Go

Declare @person char(1)
Declare @permutation int = 1
Declare perPerson Cursor Local Fast_Forward For
	Select Distinct Person From #Temp

Open perPerson
Fetch Next From perPerson Into @person

While @@Fetch_Status = 0
Begin
	-- Add every combo including this person
	Insert Into #PeoplePermutations(Person, PermutationID) 
	Exec #SubPermutations @root = @person, @current = @person, @permutation = @permutation Output
	Fetch Next From perPerson Into @person
End
Close perPerson
Deallocate perPerson

/*
Select distinct
	String_Agg(Person,',')
From #PeoplePermutations
group by PermutationID
order by 1
*/

Select 
	Person
From #PeoplePermutations PPOUTER
Join
(
	Select Top (1) 
		PermutationID
	From #PeoplePermutations Where PermutationId = Any
	(
		Select  
			PP.PermutationId
		From #PeoplePermutations PP
		Join #temp T On T.person = PP.Person
		Group By PP.PermutationID
		Having Count(Distinct T.Product) = (Select Count(Distinct Product) From #Temp)
	)
	Group By PermutationID
	Order By 
		Count(Distinct Person)
)BEST On PPOUTER.PermutationID = BEST.PermutationID