[SOLVED] selecting from three tables

[watsmyname]

i have three tables and structures are as follows

 

 

 

tbl_members

member_id      int(11)             
member_name    varchar(255)           
gender           varchar(15)          
state_id      int(11)              
city_id           int(11)              
zip_code          int(11)              
area_id         int(11)              
area_code       varchar(30)          
house_number     varchar(50)  

Another table tbl_stats

id                    int(11)      
question_id           int(11)      
member_id           int(11)     [b]<-- this field is common in first table[/b]
below5years_male      varchar(11)  
below5years_female    varchar(11)  
between6to10_male     varchar(11)  
between6to10_female   varchar(11)  
between11to15_male    varchar(11)  
between11to15_female  varchar(11)  
between16to24_male    varchar(11)  
between16to24_female  varchar(11)  
between25to45_male    varchar(11)  
between25to45_female  varchar(11)  
between45to60_male    varchar(11)  
between45to60_female  varchar(11)  
between60to75_male    varchar(11)  
between60to75_female  varchar(11)  
above75_male          varchar(11)  
above75_female        varchar(11) 

third table

tbl_area

area_id     int(11)[b]          <-- this field is common in 2nd table[/b]    
state_id  int(11)              
city_id       int(11)              
zip_code      int(11)              
area_name   varchar(100)   

 

Now all i want to do is use all these three tables to select each area name and population of male and females for that area.

 

Thanks

watsmyname

[cbolson]

Hi,

you could try something like this:

 

SELECT t1.area_name, COUNT(t2.gender) AS num_male, COUNT(t3.gender) AS num_female FROM tbl_areas AS t1 LEFT JOIN tbl_members AS t2 ON (t2.area_id=t1.area_id AND t2.gender='male')

LEFT JOIN tbl_members AS t3 ON (t3.area_id=t1.area_id AND t3.gender='female')

GROUP BY t1.area_id

 

I can't see that you need the other table for this particular result set.

 

Chris

[kickstart]

Hi

 

I agree you only need 2 tables.

 

Slightly simpler SQL, but giving you a row per gender per area :-

 

SELECT a.area_id, a.area_name, gender, genderSum

FROM tbl_area a

LEFT OUTER JOIN (SELECT area_id, gender, COUNT(member_id) AS genderSum FROM tbl_members GROUP BY area_id, gender)

ON a.area_id = b.area_id

 

All the best

 

Keith

[watsmyname]

Hi,

you could try something like this:

 

SELECT t1.area_name, COUNT(t2.gender) AS num_male, COUNT(t3.gender) AS num_female FROM tbl_areas AS t1 LEFT JOIN tbl_members AS t2 ON (t2.area_id=t1.area_id AND t2.gender='male')

LEFT JOIN tbl_members AS t3 ON (t3.area_id=t1.area_id AND t3.gender='female')

GROUP BY t1.area_id

 

I can't see that you need the other table for this particular result set.

 

Chris

well thanks for the reply. sorry for being not clear. the first table tbl_members holds the information of the family heads, 2nd table holds the number of family members of different age group in his family, and third table holds the area name. I have to show the population of males and females (from 2nd table, tbl_stats)for particular area on the basis of the info we have in first table for particular family heads. Your queries gives total number of male and female members, its not what i m looking for.

[kickstart]

Hi

 

If the fields in the 2nd table are counts of people in each age group by gender for a household then why not have them as numeric fields?

 

Assuming they are something like this would do it:-

 

SELECT a.area_id, a.area_name, SUM(c.memberMaleCount), SUM(c.memberFemaleCount)
FROM tbl_area a
INNER JOIN tbl_members b
ON a.area_id = b.area_id
INNER JOIN (SELECT member_id, below5years_male+
between6to10_male+
between11to15_male+
between16to24_male+
between25to45_male+
between45to60_male+
between60to75_male+
above75_male as memberMaleCount,
below5years_female+
between6to10_female+
between11to15_female+
between16to24_female+
between25to45_female+
between45to60_female+
between60to75_female +
above75_female as memberFemaleCount
FROM tbl_stats) c
ON b.member_id = c.member_ID
GROUP BY a.area_id, a.area_name

 

All the best

 

Keith

[watsmyname]

Hi

 

If the fields in the 2nd table are counts of people in each age group by gender for a household then why not have them as numeric fields?

 

Assuming they are something like this would do it:-

 

SELECT a.area_id, a.area_name, SUM(c.memberMaleCount), SUM(c.memberFemaleCount)
FROM tbl_area a
INNER JOIN tbl_members b
ON a.area_id = b.area_id
INNER JOIN (SELECT member_id, below5years_male+
between6to10_male+
between11to15_male+
between16to24_male+
between25to45_male+
between45to60_male+
between60to75_male+
above75_male as memberMaleCount,
below5years_female+
between6to10_female+
between11to15_female+
between16to24_female+
between25to45_female+
between45to60_female+
between60to75_female +
above75_female as memberFemaleCount
FROM tbl_stats) c
ON b.member_id = c.member_ID
GROUP BY a.area_id, a.area_name

 

All the best

 

Keith

well this did the trick, thanks a ton man, i really appreciate it.