469,356 Members | 2,322 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,356 developers. It's quick & easy.

Single Max Child Row (Based on a Count) for All Parent Rows

Let's say I have a table with a list of young folk (table1). This table is related to a table that lists all their booty calls (table2). A third table lists their booty calls' booty calls (table3). For the purposes of this example the tables are set up as a one to many relationship.

The young folks in table1 want to lower their risk of contracting an STD by kicking their single most sexually active booty call to the curb. So the challenge is to create a query that displays all the young folk in table1 along with who they should kick to the curb. Who they should kick to the curb is based on how many booty calls their booty calls have.

This is where I'm at...

select t1.ID, t2.ID,
(Select Count(t3.ID) from table3 t3 Where t3.Parent_ID = t2.ID) as BootyCalls
From table1 t1
join table2 t2 on t2.Parent_ID = t1.ID

That query displays all the rows from table1, all the rows from table2 and a BootyCall column which displays the count of table2's booty calls. The problem is that I want to display all the young folk in table1 and their single most sexually active booty call from table2.

Any ideas how to accomplish this in a single query? Thanks in advance for any help!
Jul 11 '07 #1
3 2092
Infide
28
Let's say I have a table with a list of young folk (table1). This table is related to a table that lists all their booty calls (table2). A third table lists their booty calls' booty calls (table3). For the purposes of this example the tables are set up as a one to many relationship.

The young folks in table1 want to lower their risk of contracting an STD by kicking their single most sexually active booty call to the curb. So the challenge is to create a query that displays all the young folk in table1 along with who they should kick to the curb. Who they should kick to the curb is based on how many booty calls their booty calls have.

This is where I'm at...

select t1.ID, t2.ID,
(Select Count(t3.ID) from table3 t3 Where t3.Parent_ID = t2.ID) as BootyCalls
From table1 t1
join table2 t2 on t2.Parent_ID = t1.ID

That query displays all the rows from table1, all the rows from table2 and a BootyCall column which displays the count of table2's booty calls. The problem is that I want to display all the young folk in table1 and their single most sexually active booty call from table2.

Any ideas how to accomplish this in a single query? Thanks in advance for any help!
try

Expand|Select|Wrap|Line Numbers
  1. SELECT T1.*, T2.*, bootydata.*
  2. FROM TABLE1
  3. inner join table t2
  4.     on t2.id = t1.id
  5. inner join
  6.     (SELECT Max(bootycalls), t3.id,t2.id
  7.         FROM 
  8.         (SELECT count(*) as bootycalls, t3.id, t2.id
  9.                  from table3 t3 inner join 
  10.                          table t2 on t2.id = t3.id
  11.                  GROUP BY t3.id, t2.id) as BootyData
  12. on t2.id = bootydata.id
  13.  
  14.  
The trick is the sub select which returns the max count of another sub select that performs the required aggregating.
The grouping of the derived table should provide a 1 to 1 match up of the people in table 2 with their maximum count of children in table 3.
Jul 11 '07 #2
try

Expand|Select|Wrap|Line Numbers
  1. SELECT T1.*, T2.*, bootydata.*
  2. FROM TABLE1
  3. inner join table t2
  4.     on t2.id = t1.id
  5. inner join
  6.     (SELECT Max(bootycalls), t3.id,t2.id
  7.         FROM 
  8.         (SELECT count(*) as bootycalls, t3.id, t2.id
  9.                  from table3 t3 inner join 
  10.                          table t2 on t2.id = t3.id
  11.                  GROUP BY t3.id, t2.id) as BootyData
  12. on t2.id = bootydata.id
  13.  
  14.  
Thanks! But it's missing a parentheses in this line "GROUP BY t3.id, t2.id) as BootyData" and trying to close it results in various errors such as multi-part identifiers unable to be bound and invalid columns.
Jul 11 '07 #3
Infide
28
Expand|Select|Wrap|Line Numbers
  1. SELECT T1.*, T2.*, bootydata.*
  2. FROM TABLE1
  3. inner join table t2
  4.     on t2.id = t1.id
  5. inner join
  6.     (SELECT Max(bootycalls), t3.id,t2.id
  7.         FROM 
  8.         (SELECT count(*) as bootycalls, t3.id, t2.id
  9.                  from table3 t3 inner join 
  10.                          table t2 on t2.id = t3.id
  11.                  GROUP BY t3.id, t2.id) as BootyData
  12. on t2.id = bootydata.id
  13.  
Thanks! But it's missing a parentheses in this line "GROUP BY t3.id, t2.id) as BootyData" and trying to close it results in various errors such as multi-part identifiers unable to be bound and invalid columns.
Looks like I didn't alias my columns in my derived table and I missed a group by. My bad.

The trick is creating a derived table for your third table that summarizes what you want. You need to know table 2's most frequent booty calls from table 3?

This select:
(SELECT Max(bootycalls), t3id,t2id
FROM
(SELECT count(*) as bootycalls, t3.id as t3id, t2.id as t2id
from table3 t3 inner join
table t2 on t2.id = t3.id
GROUP BY t3.id, t2.id) as BootyData
GROUP BY t3id,t2id
) as Data

This gives you the summary of highest t3 child counts to the t2 parent. Join this derived table back onto the t2 table and then t1 table.
Jul 11 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by I am Sam | last post: by
1 post views Thread by Glenn T. Kitchen | last post: by
reply views Thread by Ambica Jain | last post: by
reply views Thread by Innova | last post: by
8 posts views Thread by Rick | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.