Finding matching parents where all children also match

I'm trying to write a sql query in MS SQL Server 2008 that will match parent rows where the parents match and all their children match.

Assuming I have this basic table structure:

ParentTable: 
  ParentID, Item, Price

ChildTable:
  ChildID, ParentID, Accessory, Price

I want to get a grouping of ParentIDs where the parents match on Item and Price and they have the same number of children, each of which match on Accessory and Price.

For example:

ParentTable:
---------------------
1, "Computer", 1000
2, "Stereo"开发者_如何学运维, 500
3, "Computer", 500
4, "Computer", 1000

ChildTable:
---------------------
1, 1, "Mouse", 10
2, 1, "Keyboard", 10
3, 2, "Speakers", 50
4, 3, "Keyboard", 10
4, 3, "Mouse", 10
5, 4, "Keyboard", 10
6, 4, "Mouse", 10

The expected results would be something like

ParentID, Grouping
---------------------
1, 1
2, 2
3, 3
4, 1

This would imply that ParentID 1 and 4 are exactly the same and 2 and 3 are unique. I dont really care about the format of the result, as long as I get a list of parents that match.

I'm not opposed to doing (some or all of) this in .net either.

---

your question is a little ambiguous, but I thought I'd give it a shot anyway.

here goes. Free form SQL. Hard to get it exactly right without access to some DML.

So this would be my general approach. This should work in SQL Server, probably Oracle as well. I'm not claiming this is perfect. My mental schema doesn't match above exactly, I'll leave that as an exercise for the reader. I typed it straight in.

SELECT DISTINCT p.id,p.name,p.dte,q.cnt
FROM parent p
JOIN
 (
  select p.id, p.dte, count(*) cnt
  from parent p 
  join child ch 
  on ch.pid = p.id
  group by p.id, p.dte
 ) q
 ON p.id=q.id and p.dte=q.dte
GROUP BY p.id,p.name,q.cnt
ORDER BY p.id,p.name,q.cnt

btw: your question is a little ambiguous.

---

UPDATE:

this function looks promissing for the child rows to csv direction

http://sql-ution.com/function-to-convert-rows-to-csv/

---

OK if you can do this with temp tables, then this may give you ideas. Off the top of my head, so syntax not checked. Also, this is limited, as bigint typically only goes up to 2**63 or something. First put unique child accessory and price into #child

create table #child ( accessory varchar, price decimal, id identity, 
bnumber bigint null)
insert into #child(accessory, price) 
select accesory,price from childtable group by accessory price

assuming id will be 1,2,3,4 etc

update #child set bnumber = 2**(id-1)

sets bnumber to 1,2,4,8 etc (this is where the bigint limitation may kick in). So now you have

mouse,   10,1,1 
keyboard,10,2,2 
speakers,50,3,4

Now you can sum these numbers by parent

select p.item, sum(ctemp.bnumber)
from parent p, child c, #child ctemp
where p.parentid = c.parentid
and   c.accessory = ctemp.accessory
and   c.price     = ctemp.price
group by p.item

giving

1, 3
2, 4
3, 3
4, 3

..which I think is the answer you want. This is a bit clunky, and it's been a long day(!), but it might help.