CODEWITHSUNDEEP
mysqlsql
ali79
ali79

Reputation: 23

mysql query to fetch records from multiple tables and join

I have 5 tables named: schools, candidates, candidate_subjects, subjects, lgas Each school belong to a lga, each candidate belong to a school, each candidate registers subjects Below are the table structures:

CREATE TABLE `subjects` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(250) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  `code` varchar(10) DEFAULT NULL,
  `exam_type_id` int(11) DEFAULT NULL,
  `status` int(11) DEFAULT NULL,
  `type` varchar(3) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=60 DEFAULT CHARSET=latin1;

CREATE TABLE `candidates` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `exam_no` varchar(20) DEFAULT NULL,
  `surname` varchar(100) DEFAULT NULL,
  `other_names` varchar(150) DEFAULT NULL,
  `school_id` int(11) DEFAULT NULL,
  `exam_type_id` int(11) DEFAULT NULL,
  `dob` varchar(12) DEFAULT NULL,
  `sex` varchar(6) DEFAULT NULL,
  `no_of_subjects` int(2) DEFAULT NULL,
  `nationality` varchar(20) DEFAULT NULL,
  `state` varchar(20) DEFAULT NULL,
  `lga` varchar(20) DEFAULT NULL,
  `exam_year` varchar(4) DEFAULT NULL,
  `date_created` varchar(255) DEFAULT NULL,
  `date_modified` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `registration_completed` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=28034 DEFAULT CHARSET=latin1;

CREATE TABLE `candidate_subjects` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `candidate_id` int(11) DEFAULT NULL,
  `exam_type_id` int(10) DEFAULT NULL,
  `subject_id` int(10) DEFAULT NULL,
  `ca_score` int(11) DEFAULT NULL,
  `exam_score` int(6) DEFAULT NULL,
  `score_grade` varchar(10) DEFAULT NULL,
  `date_created` varchar(10) DEFAULT NULL,
  `date_modified` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=327740 DEFAULT CHARSET=latin1;

CREATE TABLE `schools` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `passcode` varchar(15) DEFAULT NULL,
  `code` varchar(10) DEFAULT NULL,
  `name` varchar(200) DEFAULT NULL,
  `lga` int(11) DEFAULT NULL,
  `address` varchar(250) DEFAULT NULL,
  `phone` varchar(50) DEFAULT NULL,
  `exam_year` varchar(10) DEFAULT NULL,
  `eo_name` varchar(100) DEFAULT NULL,
  `eo_phone` varchar(50) DEFAULT NULL,
  `eo_email` varchar(100) DEFAULT NULL,
  `date_created` varchar(10) DEFAULT NULL,
  `date_modified` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `profile_created` int(1) DEFAULT NULL,
  `entries_purchased` int(11) DEFAULT NULL,
  `entries_used` int(11) DEFAULT NULL,
  `entries_remaining` int(11) DEFAULT NULL,
  `scratchcard_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=652 DEFAULT CHARSET=latin1;

CREATE TABLE `lgas` (
  `lga_id` int(11) NOT NULL AUTO_INCREMENT,
  `lga_name` varchar(250) NOT NULL,
  `state_id` varchar(20) NOT NULL,
  PRIMARY KEY (`lga_id`)
) ENGINE=InnoDB AUTO_INCREMENT=786 DEFAULT CHARSET=latin1;

I want to generate a report for each lga like this:

S/No| Name of School |Eng|Mth|B.sc|....Total registered

That is name of school from the selected lga, total number of students from that school that registered for Eng, Mth, B.sc ... Total number of students that registered those subjects from that school

Upvotes: 0

Views: 31

Answers (1)

Joshua Stafford
Joshua Stafford

Reputation: 635

SELECT 
  b.name as school_name, 
  sum(case when e.name = 'Eng' then 1 else 0 end) as english_students,
  sum(case when e.name = 'Mth' then 1 else 0 end) as math_students,
  sum(case when e.name = 'B.sc' then 1 else 0 end) as whatever_this_is_students
FROM lgas a
LEFT JOIN schools b ON a.lga_id = b.lga
LEFT JOIN candidates c on b.id = c.school_id
LEFT JOIN candidate_subjects d on c.id = d.candidate_id
LEFT JOIN subjects e on d.subject_id = e.id
WHERE a.lda_id = 'selected_id'
GROUP by school_name;

Upvotes: 1

Related Questions