mysql query to fetch records from multiple tables and join

Question

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

Sounds like an interesting project, let us know if you have a question! — Shadow
– Shadow, Commented Mar 22, 2016 at 14:56

Joshua Stafford · Accepted Answer · 2016-03-22 17:16:10Z

1

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;

edited Mar 22, 2016 at 17:16

answered Mar 22, 2016 at 15:06

Joshua Stafford

6653 gold badges8 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

mysql query to fetch records from multiple tables and join

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related