How can I retrieve data from two tables with a single query?

I need a list of activities showing the number of children of each genderregistered for each activity.

The Structure:

CREATE TABLE Child(
child_id INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
pc_id INTEGER NOT NULL,
child_fname VARCHAR(20) NOT NULL,
child_lname VARCHAR(20) NOT NULL,
child_dob DATE NOT NULL,
child_gender ENUM ('F','M') DEFAULT 'F' NOT NULL,
CONSTRAINT FOREIGN KEY(pc_id) REFERENCES Parent_Carer(pc_id))
ENGINE=InnoDB; 

CREATE TABLE Child_Activity(
child_id INTEGER NOT NULL,
activity_name_id ENUM('Art','Football','IT') DEFAULT 'IT'  NOT NULL,
activity_day ENUM('Tuesday','Wednesday','Thursday') NOT NULL,
CONST开发者_JAVA百科RAINT PRIMARY KEY(child_id,activity_name_id),
CONSTRAINT FOREIGN KEY(child_id)references Child(child_id),
CONSTRAINT FOREIGN KEY(activity_name_id)references Activity(activity_name_id) )
ENGINE=InnoDB;

I have this query

SELECT activity_name_id,
       COUNT(*)     AS 'Number of Children',
       child_gender AS 'Childs Gender'
FROM   child c,
       child_activity ca
WHERE  c.child_id = ca.child_id
GROUP  BY child_gender,
          activity_name_id;  

Thank you.

Select Child_Activity.activity_name_id, count(child_m.id) as boys,   count(child_f.id) as girls,   
FROM Child_Activity
INNER JOIN Child as child_m
ON (child_m.child_id = Child_Activity.child_id AND child_m.gender = 'M' )
INNER JOIN  Child child_f
ON (child_f.child_id = Child_Activity.child_id AND child_f.gender = 'F' )

I hope this works

Use joins

For example

SELECT * FROM Child AS c
JOIN Child _Activity as ca
ON c.child_id = ca.child_id

Google 'mysql table joins' for more examples. You need to replace fields below with the names of the fields you want returned.

SELECT fields FROM Child AS C
INNER JOIN Child_Activity AS CA
ON CA.child_id = C.child_id