I'd love a bit of help with this chestnut.

I have a table (let us call it 'table_details') related to another table (let us call it 'table_parent') in a many to one relationship. That is, a parent can have many details. Table_details has a field called [GROUP], in which I am interested.

I need to extract from table_details only those records where there are 3 or more distinct [GROUP]s related to any given parent via the foriegn key field.

I can count how many table_details records relate to each parent thus:


Select count(table_detail.[GROUP])
From table_detail
Group By table_detail.TheForiegnKey


Although, this doesn't give me a distinct count, which is what I need.

My main problem however is that I can't seem to get anything useful out of table_detail by including the query detailed above as a subquery. I've tried the following but I just can't seem to get 'InnerCountOfGroups' returned as part of the outer query:



SELECT DetailOuter.ThePrimaryKey, DetailOuter.TheForiegnKey
FROM table_detail AS DetailOuter
WHERE ((Select count(DetailInner.[_GROUP_]) AS InnerCountOfGroups
FROM table_detail AS DetailInner
WHERE DetailInner.TheForiegnKey = DetailOuter.TheForiegnKey
GROUP BY DetailInner.TheForiegnKey))
GROUP BY DetailOuter.TheForiegnKey, DetailOuter.ThePrimaryKey;


I've tried a HAVING clause instead of the red WHERE clause above, but that gave me no joy. I believe I need to return InnerCountOfGroups so that I can have an outer WHERE clause that filters based on InnerCountOfGroups being greater than 3.

Any assistance greatly appreciated. Perhaps there's another way to crack this chestnut.

If it matters (which I suspect it might) I am using Access to front end an SQL Server db.

- Dave Cody

This will give the foreign key and the number of distinct groups for that key where there is more than 2 distincy groups for that key

SELECT TheForiegnKey, COUNT(*) AS Expr1
FROM (SELECT DISTINCT ForiegnKey, [_Group_]
                       FROM table_detail
                       GROUP BY ForiegnKey, [_Group_]) DERIVEDTABLE
GROUP BY ForiegnKey
HAVING (COUNT(*) > 2)

Thanks very much. That suggestion accurately returns the records where TheForiegnKey has multiple entries in table_detail. Good stuff.

What I really need to do is write an efficient query that will not only count where a parent has multiple children (>2), but I then need to be able to join this count of multiple children to a number of other fields in other tables.

I can select all the children fields in table_detail I need to join to the other tables within the inner select statement, but this takes a LOOOONG time to run.

I thought about using some kind of JOIN from DERIVEDTABLE to table_detail based on table_detail.PrimaryKey, but this seems complex and may not return the expected results. Is there another cunning approach I could take here?

Any help greatly appreciated.

Use a temporary table to hold the results of the initial query, something like

SELECT TheForiegnKey, COUNT(*) AS Expr1
INTO #temptable
FROM (SELECT DISTINCT ForiegnKey, [_Group_]
         FROM table_detail
         GROUP BY ForiegnKey, [_Group_]) DERIVEDTABLE
GROUP BY ForiegnKey
HAVING (COUNT(*) > 2)

select whatever, whatever
FROM OTHERTABLE O, #Temptable T
Where O.Somefield = T.TheForiegnKey

I haven't checked this syntax but I think it should work.