CODEWITHSUNDEEP
mysqlsqldatabase
StackOverflowNewbie
StackOverflowNewbie

Reputation: 40633

MySQL: searching children records

Assume 2 tables:

TABLE: team
-----------
| team_id |
===========
| 1       |
-----------
| 2       |
-----------

TABLE: team_member
-------------------------------------------
| team_member_id | team_id | age | gender |
===========================================
| 1              | 1       | 20  | Male   |
-------------------------------------------
| 1              | 2       | 25  | Male   |
-------------------------------------------
| 1              | 2       | 23  | Female |
-------------------------------------------

How do I search for things like this:

  1. List of all teams that are only male, between ages of 18 and 25.
  2. List of all teams that are male and female, where either gender is between ages of 18 and 25.
  3. Etc.

I'm basically looking for general strategies on how to apply certain filters (e.g. age) across all the children records of team. Suggestions?

Upvotes: 0

Views: 217

Answers (4)

DRapp
DRapp

Reputation: 48139

team, team_member

You can't just apply a simple WHERE clause if you are checking for only an all male or all female team... it needs to be compared against the full set of team members. Try

select 
      tm.team_id,
      sum( if( tm.gender = 'Male' AND tm.age between 18 and 25, 1, 0 )) as MaleInRange,
      count(*) AllTeamMembers
  from
      team_member tm
  group by
      tm.Team_ID
  having
      MaleInRange = AllTeamMembers

Similarly, to ensure you have BOTH male AND female...

select 
      tm.team_id,
      sum( if( tm.age between 18 and 25, 1, 0 )) as AgeInRange,
      sum( if( tm.gender = 'Male', 1, 0 )) as MaleCnt,
      sum( if( tm.gender = 'Female', 1, 0 )) as FemaleCnt,
      count(*) AllTeamMembers
  from
      team_member tm
  group by
      tm.Team_ID
  having
         AgeInRange = AllTeamMembers
     AND MaleCnt > 0
     AND FemaleCnt > 0

Upvotes: 1

moskito-x
moskito-x

Reputation: 11958

BTW team_member_id must be unique or this column is useless !!

List of all teams that are male and female, where either gender is between ages of 18 and 25.

SELECT * FROM team_member WHERE Age BETWEEN 18 AND 25 GROUP BY team_id.

result

| team_member_id | team_id | age | gender |
===========================================
| 1              | 1       | 20  | Male   |
-------------------------------------------
| 1              | 2       | 25  | Male   |
-------------------------------------------

Upvotes: 0

maolddv
maolddv

Reputation: 97

You can do it as below:
1. SELECT * FROM T2 WHERE Gender='Male' and Age BETWEEN 18 AND 25.
2. SELECT * FROM T2 WHERE Age BETWEEN 18 AND 25.
Hope it helps!

Upvotes: 0

FSP
FSP

Reputation: 4827

Something like - SELECT * FROM t1 WHERE key_col > 1 AND key_col < 10;

Check this example - http://www.roseindia.net/sql/mysql-example/mysql-range.shtml. Hope it helps!

Upvotes: 0

Related Questions