I tried Inner join, however i m not able to figure out what is wrong i m doing. How can I query data from all the 6 tables in the blue box.
The studentprofile table is what is the link between the other 5 tables in the blue region.
I ran this query and this showed me all the data.
select * from users, roles_assigned,studentprofile,schoolwithusers;
Problem I am facing is that I need only filted data for user who are students and school name and student profile Please Help. here is my sql code.
--
-- Database: `onlinemarksheets`
--
-- --------------------------------------------------------
--
-- Table structure for table `exams`
--
CREATE TABLE `exams` (
`id` int(11) NOT NULL,
`examtye_id` int(11) DEFAULT NULL,
`schooluser_id` int(11) DEFAULT NULL,
`duration_to` date DEFAULT NULL,
`duration_from` date DEFAULT NULL,
`year` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- --------------------------------------------------------
--
-- Table structure for table `examtype`
--
CREATE TABLE `examtype` (
`id` int(11) NOT NULL,
`type` enum('Mid-Term','Half-yearly','Yearly') DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Dumping data for table `examtype`
--
INSERT INTO `examtype` (`id`, `type`) VALUES
(1, 'Mid-Term'),
(2, 'Half-yearly'),
(3, 'Yearly');
-- --------------------------------------------------------
--
-- Table structure for table `marks`
--
CREATE TABLE `marks` (
`id` int(11) NOT NULL,
`exam_id` int(11) DEFAULT NULL,
`name` varchar(45) DEFAULT NULL,
`marks_obtained` int(11) DEFAULT NULL,
`marks_total` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- --------------------------------------------------------
--
-- Table structure for table `roles`
--
CREATE TABLE `roles` (
`id` int(11) NOT NULL,
`type` enum('Admin','Teacher','Student') DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Dumping data for table `roles`
--
INSERT INTO `roles` (`id`, `type`) VALUES
(1, 'Admin'),
(2, 'Teacher'),
(3, 'Student');
-- --------------------------------------------------------
--
-- Table structure for table `roles_assigned`
--
CREATE TABLE `roles_assigned` (
`id` int(11) NOT NULL,
`role_id` int(11) DEFAULT NULL,
`user_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Dumping data for table `roles_assigned`
--
INSERT INTO `roles_assigned` (`id`, `role_id`, `user_id`) VALUES
(1, 1, 1),
(2, 2, 2),
(3, 3, 3),
(4, 3, 4),
(5, 3, 5),
(6, 3, 6),
(7, 1, 1),
(8, 2, 2),
(9, 3, 3),
(10, 3, 4),
(11, 3, 5),
(12, 3, 6);
-- --------------------------------------------------------
--
-- Table structure for table `schools`
--
CREATE TABLE `schools` (
`id` int(11) NOT NULL,
`schoolname` varchar(45) DEFAULT NULL,
`school_email` varchar(45) DEFAULT NULL,
`school_phone` varchar(45) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Dumping data for table `schools`
--
INSERT INTO `schools` (`id`, `schoolname`, `school_email`, `school_phone`) VALUES
(1, 'D.A.V Public school', '[email protected]', '789456123'),
(2, 'saraswati Public school', '[email protected]', '9998887774'),
(3, 'S.G.R.R Public School', '[email protected]', '54245645125'),
(4, 'Sun Valley Public', '[email protected]', '23423423424'),
(5, 'Marshal Public school', '[email protected]', '23482728347');
-- --------------------------------------------------------
--
-- Table structure for table `schoolwithusers`
--
CREATE TABLE `schoolwithusers` (
`id` int(11) NOT NULL,
`school_id` int(11) DEFAULT NULL,
`studentprofile_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Dumping data for table `schoolwithusers`
--
INSERT INTO `schoolwithusers` (`id`, `school_id`, `studentprofile_id`) VALUES
(1, 1, 3),
(2, 3, 4);
-- --------------------------------------------------------
--
-- Table structure for table `studentprofile`
--
CREATE TABLE `studentprofile` (
`id` int(11) NOT NULL,
`user_id_fk` int(11) DEFAULT NULL,
`rollno` int(11) DEFAULT NULL,
`dob` date DEFAULT NULL,
`attendence` int(11) DEFAULT NULL,
`class` int(11) DEFAULT NULL,
`section` enum('A','B') DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Dumping data for table `studentprofile`
--
INSERT INTO `studentprofile` (`id`, `user_id_fk`, `rollno`, `dob`, `attendence`, `class`, `section`) VALUES
(1, 3, 22, '2022-01-21', 60, 1, 'A'),
(2, 4, 21, '2012-01-04', 45, 1, 'A'),
(3, 4, 1, '2012-01-04', 100, 3, 'B'),
(4, 5, 30, '2007-04-26', 45, 3, 'B'),
(5, 6, 2, '2022-01-19', 50, 6, 'B');
-- --------------------------------------------------------
--
-- Table structure for table `users`
--
CREATE TABLE `users` (
`id` int(11) NOT NULL,
`fname` varchar(45) DEFAULT NULL,
`lname` varchar(45) DEFAULT NULL,
`email` varchar(45) DEFAULT NULL,
`password` varchar(45) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Dumping data for table `users`
--
INSERT INTO `users` (`id`, `fname`, `lname`, `email`, `password`) VALUES
(1, 'Shashank', 'Naithani', '[email protected]', 'lol123'),
(2, 'Kuldeep', 'Negi', '[email protected]', 'lop123'),
(3, 'Arpit', 'Thakut', '[email protected]', 'arp123'),
(4, 'Ankit', 'Barthwal', '[email protected]', 'ankit123'),
(5, 'Mukesh', 'Thakur', '[email protected]', 'sha123'),
(6, 'Arjun', 'Negi', '[email protected]', 'sha123');
--
-- Indexes for dumped tables
--
--
-- Indexes for table `exams`
--
ALTER TABLE `exams`
ADD PRIMARY KEY (`id`),
ADD KEY `examtye_id` (`examtye_id`) USING BTREE,
ADD KEY `schooluser_id` (`schooluser_id`);
--
-- Indexes for table `examtype`
--
ALTER TABLE `examtype`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `marks`
--
ALTER TABLE `marks`
ADD PRIMARY KEY (`id`),
ADD KEY `exam_id` (`exam_id`);
--
-- Indexes for table `roles`
--
ALTER TABLE `roles`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `roles_assigned`
--
ALTER TABLE `roles_assigned`
ADD PRIMARY KEY (`id`),
ADD KEY `role_id` (`role_id`) USING BTREE,
ADD KEY `user_id` (`user_id`) USING BTREE;
--
-- Indexes for table `schools`
--
ALTER TABLE `schools`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `schoolwithusers`
--
ALTER TABLE `schoolwithusers`
ADD PRIMARY KEY (`id`),
ADD KEY `school_id` (`school_id`) USING BTREE,
ADD KEY `studentprofile_id` (`studentprofile_id`);
--
-- Indexes for table `studentprofile`
--
ALTER TABLE `studentprofile`
ADD PRIMARY KEY (`id`),
ADD KEY `user_id_idx` (`user_id_fk`);
--
-- Indexes for table `users`
--
ALTER TABLE `users`
ADD PRIMARY KEY (`id`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `exams`
--
ALTER TABLE `exams`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `examtype`
--
ALTER TABLE `examtype`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;
--
-- AUTO_INCREMENT for table `marks`
--
ALTER TABLE `marks`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `roles`
--
ALTER TABLE `roles`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;
--
-- AUTO_INCREMENT for table `roles_assigned`
--
ALTER TABLE `roles_assigned`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=13;
--
-- AUTO_INCREMENT for table `schools`
--
ALTER TABLE `schools`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;
--
-- AUTO_INCREMENT for table `schoolwithusers`
--
ALTER TABLE `schoolwithusers`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;
--
-- AUTO_INCREMENT for table `studentprofile`
--
ALTER TABLE `studentprofile`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;
--
-- AUTO_INCREMENT for table `users`
--
ALTER TABLE `users`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;
--
-- Constraints for dumped tables
--
--
-- Constraints for table `exams`
--
ALTER TABLE `exams`
ADD CONSTRAINT `examtye_id` FOREIGN KEY (`examtye_id`) REFERENCES `examtype` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
ADD CONSTRAINT `schooluser_id` FOREIGN KEY (`schooluser_id`) REFERENCES `schoolwithusers` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
--
-- Constraints for table `marks`
--
ALTER TABLE `marks`
ADD CONSTRAINT `exam_id` FOREIGN KEY (`exam_id`) REFERENCES `exams` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
--
-- Constraints for table `roles_assigned`
--
ALTER TABLE `roles_assigned`
ADD CONSTRAINT `role_id` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
ADD CONSTRAINT `user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
--
-- Constraints for table `schoolwithusers`
--
ALTER TABLE `schoolwithusers`
ADD CONSTRAINT `school_id` FOREIGN KEY (`school_id`) REFERENCES `schools` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
ADD CONSTRAINT `studentprofile_id` FOREIGN KEY (`studentprofile_id`) REFERENCES `studentprofile` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
--
-- Constraints for table `studentprofile`
--
ALTER TABLE `studentprofile`
ADD CONSTRAINT `user_id_fk` FOREIGN KEY (`user_id_fk`) REFERENCES `users` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
COMMIT;
CodePudding user response：
You have to join each table on the primary key/foreign key. Some of the sample data is not quite correct because you have duplicate combinations of student_id and roles_id values in the roles_assigned table. Be sure to list the column names you need instead of *.
select *
from users
INNER JOIN roles_assigned ON users.id = roles_assigned.user_id
INNER JOIN roles ON roles.id = roles_assigned.role_id
INNER JOIN studentprofile on studentprofile.id = users.id
INNER JOIN schoolwithusers on schoolwithusers.studentprofile_id =
studentprofile.id
INNER JOIN schools ON schools.id = schoolwithusers.school_id
WHERE roles.type = 'Student';