List:General Discussion« Previous MessageNext Message »
From:Frank Date:April 10 2006 9:45am
Subject:Re: Query help with count and join on same table I think
View as plain text  
2wsxdr5 wrote:

> I have a table of people.  Some of the people in this table are
> related.  You can find out who is related by comparing a familyID
> number.  I have a query to select a certain group of people from the
> table and I want to also select anyone who is related to them, even
> though those who are related will not match the other criteria.  So my
> table is something like this....
> 
> people{
> PID,
> Name
> FamilyID,
> BirthDate,
> Sex
> Address
> . . .
> )
> 
> I have a query like this....
> Select * from people where BirthDate < 1987 and Birthday > 1950 and
> address = "xyz"
> 
> I need to change it so it includes everyone that has the same Family ID
> as each person that query will return and group it by Family ID
> 

Hi Chris,

if you are using a new enough version (4.1 or newer) you can use a subquery.
If I understand your intention correctly, the following statement should do
what you need:

select familyid, count(PID) from people where FamilyID = (select FamilyID
from people where BirthDate < 1987 and Birthday > 1950 and address = "xyz")
group by FamilyId

If your version does not support sub-queries, have at look at
http://dev.mysql.com/doc/refman/4.1/en/rewriting-subqueries.html

Cheers
Frank


Thread
Query help with count and join on same table I think2wsxdr58 Apr
  • Re: Query help with count and join on same table I thinkFrank10 Apr