Best SQL query to get common children that exists for all parents

I have two tables, Parent(#tableA) and Child(#tableB)

In the Script given below I need to get only one r开发者_StackOverflowecord for this example 'C3' which is a common child for P1, P2 and P3.

I could only think of this method below.

Create table #tableA ( PkCol varchar(3) )
Create table #tableB ( FkCol varchar(3), FKChild varchar(10) )

Insert Into #tableA
Select 'P1' union
Select 'P2' union
Select 'P3' 

Insert Into #tableB
Select 'P1', 'C1' union
Select 'P1', 'C2' union
Select 'P1', 'C3' union
Select 'P2', 'C3' union
Select 'P2', 'C4' union
Select 'P2', 'C5' union
Select 'P3', 'C3' union
Select 'P3', 'C6' union
Select 'P3', 'C7' 

Select tb.FKChild from #tableA ta Inner Join #tableB tb on tb.FkCol = ta.PkCol
Group By tb.FKChild
Having Count(Distinct ta.PkCol) = (Select Count(*) from #tableA)

Drop table #tableA
Drop table #tableB

Is there a better way to do this that is clean and that will not affect the performance when the actual parent table has close to 1K records and child table has say close to 2 million plus records?

---

First check your query plan. If you're quite sure, that the proportion of the tables is as you described 1k : 2M, and your query plan says something else, you may add a hint to the join as described here: http://msdn.microsoft.com/en-us/library/ms173815%28v=SQL.90%29.aspx

If both the tables fit in memmory, try using hash join, if not go for loop-join (based on your query):

Select tb.FKChild from #tableA ta Inner Loop Join #tableB tb on tb.FkCol = ta.PkCol
Group By tb.FKChild
Having Count(Distinct ta.PkCol) = (Select Count(*) from #tableA)

Note however, that if you are unsure of the data ratio, leave the query plan to choose by the sql server, as it may affect your performance if you force to use incorrect type of join.