MySQL Count If using 4 tables or Perl
- by user1726133
Hi I have a relatively convoluted query that relies on 4 different tables, unfortunately I do not have control of this data, but I do have to query it.
I ran this simpler query and it works using just table 1 and table 2
SELECT actor, receiver, count(IF(t2.group1 = "anxiety behavior", 1,0)) AS 'anxiety'
FROM ethogram_edited_obs_behaviors t1
JOIN ethogram_behaviors t2 on t1.behavior = t2.behavior_code
GROUP BY actor;
Below are the 4 tables I need and the query I tried that didn't work
Table 1 | Table 2 | Table 3 | Table 4
Actor | Behavior | Behavior | type of Behavior | subject | sex | subject |subject_code
er frown | frown anxiety behavior | Eric M | Eric | er
Here is the query that is failing
SELECT actor, count(IF(t2.group1 = "anxiety behavior", 1,0) AND(t3.sex = "M", 1,0)) AS 'anxiety',
FROM ethogram_edited_obs_behaviors t1
JOIN ethogram_behaviors t2 on t1.behavior = t2.behavior_code
JOIN subject_code t3 on t1.actor = t3.behavior_code1
JOIN subjects t4 on t3.subject = t4.yerkes_code
GROUP BY actor;
Any help would be much appreciated!! Thanks :)
P.S. if this is easier to do in Perl tips also much appreciated
