Basic Querying - counting child records

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Basic Querying - counting child records

Author

Topic

deco10
Starting Member

28 Posts

Posted - 2010-11-26 : 14:22:23

Hi,
I'm trying to get all parent records that have x number of records in a join table. I'm not sure of the best way to do this. I know there are several ways...

This is my query, it works but I'd like to do it without the sub query

Find all the people that have more than 2 houses in the join table.

Tables:
People (id, name)
Houses (id, address)
PeopleHouses (person_id, house_id)

SELECT * FROM people WHERE (SELECT * people_houses WHERE person_id = people.id) > 2;

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2010-11-26 : 14:30:45

That query doesn't work at all, I am fairly certain.


SELECT people.name
FROM people INNER JOIN people_houses 
  on people.ID = people_houses.person_id
Group by People.name
Having COUNT(*) >2

Smells like homework.

Poor planning on your part does not constitute an emergency on my part.

deco10
Starting Member

28 Posts

Posted - 2010-11-26 : 14:38:50

This is perfect. And no it wasn't homework I just mocked a simple example so as not to reveal the nature of my work, not sure why.

Thanks much!

deco10
Starting Member

28 Posts

Posted - 2010-11-26 : 14:53:16

Also is it possible, with this query to:
a) capture the count
I thought something like HAVING (count(*) as xyz) > 2 might work... but not.

b)
only count child records where people_houses.createdat > today (or any other condition). I can't see how to do this withought a sub query.

Thanks again for any input you can provide.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2010-11-26 : 15:08:35

SELECT people.name
FROM people INNER JOIN people_houses
on people.ID = people_houses.person_id
Group by People.name
Having COUNT(*) >2 and people_houses.createdat < getdate()

Poor planning on your part does not constitute an emergency on my part.

deco10
Starting Member

28 Posts

Posted - 2010-11-28 : 23:40:26

What if we change the people_houses table to be PeopleHouses (person_id, house_id, role_type)
I want all the houses where no people_house has a role_type of 2

SELECT people.name
FROM people INNER JOIN people_houses
on people.ID = people_houses.person_id
Group by People.name
Having COUNT(*) > 2 and people_houses.createdat < getdate()
// something like people_houses.role_type != 2

TechNess
Starting Member

11 Posts

Posted - 2010-11-29 : 02:01:21

Modifing your query slightly should work:

SELECT people.name
FROM people INNER JOIN people_houses
on people.ID = people_houses.person_id
Group by People.name
Having COUNT(*) > 2 and people_houses.createdat < getdate() and people_houses.role_type NOT IN (2)

**TechNess**

Jennifercma
Starting Member

2 Posts

Posted - 2013-06-02 : 12:18:17

unspammed

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources