Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
| Search | Today's Posts | Mark Forums Read
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old April 27th, 2004, 08:42 AM
Registered User
 
Join Date: Apr 2004
Location: Dublin, , Ireland.
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Select Where Greater Than Count

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
  #2 (permalink)  
Old April 28th, 2004, 05:29 AM
Authorized User
 
Join Date: Apr 2004
Location: , , Ireland.
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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)

  #3 (permalink)  
Old April 28th, 2004, 10:44 AM
Registered User
 
Join Date: Apr 2004
Location: Dublin, , Ireland.
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

  #4 (permalink)  
Old April 29th, 2004, 05:14 AM
Authorized User
 
Join Date: Apr 2004
Location: , , Ireland.
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.



Similar Threads
Thread Thread Starter Forum Replies Last Post
Select COUNT(*) grstad Classic ASP Basics 5 April 28th, 2006 11:01 AM
How to get "SELECT COUNT(*)" return information? datagram ASP.NET 1.0 and 1.1 Basics 3 December 23rd, 2004 01:21 AM
SELECT COUNT as a ROW?? t0ny SQL Language 3 December 8th, 2004 05:31 AM
How to execute SELECT count(*) lxu Access 2 April 8th, 2004 08:16 AM
COUNT ON SELECT DISTINCT stmt savoym SQL Language 7 August 28th, 2003 07:58 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.