Help on a farmer database (COUNT on JOINT tables)

[bobbymoir]

i have some problems with mysql database query and its as below :

Tables :

i) departments
id
department
description

ii) farmers
id
name
sex
address
age
category
remarks

iii) scientists
id
departmentid
name
remarks

iv) scientistvisits
id
scientistid
farmerid
datevisited
purpose
remarks

Here, i want the query of number of scientist visits in a detailed way, i.e., number of visits, department wise, male farmers, female farmers, farmer category wise, total number of males, total number of females, grand total.

Please advise me for the best code for this query.

Thanking you in advance
Bobby

[darkagn]

Why not do each in a separate query? For example, to find the number of male farmers:

select count(*) from farmers where sex = 'male'

All the other queries will be similar, just substitute farmers for whichever table you are querying and change your where clause appropriately.

[bobbymoir]

Why not do each in a separate query? For example, to find the number of male farmers:

select count(*) from farmers where sex = 'male'

All the other queries will be similar, just substitute farmers for whichever table you are querying and change your where clause appropriately.

That produces the number of male farmers from the farmers table and not the number of farmers visited by a scientist from the scientistvisits table. What i want is the number of visits to the farmers (male, female, category wise, etc.) from the scientistvisits table.

[darkagn]

Ah, sorry I misunderstood. So your query will require a join to the scientistvisits table, joining on farmerid, but only where the farmer is male (for example). I will use a left outer join here because if the farmers have had no visits, we want to return a result of 0 rather than simply missing them (as an inner join would). So the syntax looks like this:

select count(*) from farmers f
left outer join scientistvisits v
on f.id = v.farmerid
where f.sex = 'male'
group by f.id

EDIT: The group by clause here will group the results by the farmer, so this way you can see the number of visits for each farmer. If you remove the group by clause, the result will be a single row, giving the total number of visits to male farmers, in which case an inner join would be more efficient.