Mysql query help, getting values ​​from a table using a relational identifier from another table

Question

SQL THAT CREATES TABLES

-- -- Table structure for table `careers` -- CREATE TABLE IF NOT EXISTS `careers` ( `career_id` int(11) NOT NULL auto_increment, `career_name` varchar(75) NOT NULL, `career_desc` text NOT NULL, `degree_needed` enum('Yes','No') NOT NULL, `useful_info` text, `useful_links` text, PRIMARY KEY (`career_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=34 ; -- -------------------------------------------------------- -- -- Table structure for table `course` -- CREATE TABLE IF NOT EXISTS `course` ( `course_id` int(11) NOT NULL auto_increment, `course_type` varchar(75) NOT NULL, `course_names` text NOT NULL, `extra_needed` enum('Yes','No') default NULL, `course_link` varchar(150) NOT NULL, `grades_grade_id` int(11) NOT NULL, PRIMARY KEY (`course_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=87 ; -- -------------------------------------------------------- -- -- Table structure for table `grades` -- CREATE TABLE IF NOT EXISTS `grades` ( `grade_id` int(11) NOT NULL auto_increment, `grade_desc` text NOT NULL, `careers_career_id` int(11) NOT NULL, PRIMARY KEY (`grade_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=87 ; -- -------------------------------------------------------- 

An overview of my theory behind tables is that each class is associated with a career, and one career may have many grades, of which one course is associated with only one course, but the user may need to take an additional course if the one they choose is not sufficiently accredited .

So my question is how to choose course details for higher level courses if the user selects a low level course,

for example, the user wants to be an electrician, and they have 2 class D at school, which means that they can only do a level 2 course, which means that they must take a higher level course to complete the course. I need to show that other courses are based on the fact that they chose an electrician and a level 2 course, it is worth noting that courses that require additional work have an extra_needed field that is marked as yes.

I can’t live or work on how to get the data I need, I tried the following:

 SELECT * FROM `course` , `grades` , `careers` WHERE `course`.`extra_needed` IS NULL AND `grades`.`grade_id` = `careers`.`career_id` AND `careers`.`career_id` =6 

however, this returns 59 rows of data, where it should return 2 rows of data, and the other in the data rows that the user could select if they had selected other choices.

Answer 1

It looks like you are joining the wrong fields, the relationship looks like this:

 careers.career_id = grades.careers_career_id grades.grade_id = course.grades_grade_id 

therefore, for all career-related courses .career_id = 6, the request will look like this:

 select course.* from course, careers, grades where course.grades_grade_id = grades.grade_id and grades.careers_career_id = careers.career_id and careers.career_id = 6 

You will need a more complex query to complete what you initially asked, although this involves not only showing a career, but also course_id, and then a conditional statement to say if any additional courses are needed, but I'm not sure you have all the fields necessary for this, since you need to know the relationship between the selected course and all other courses related to the corresponding career. If you just want to see all the other courses related to this career, you will add a line, for example:

 and course.course_id <> (The course they have selected) 

If there are only two levels of courses, you can add a line as shown below, as if they have chosen a higher level that cannot satisfy both the last and this, whereas if they chose the lower level, both will be True:

 and course.extra_needed IS NULL 

Answer 2

Replace your request with this:

  SELECT *
 FROM careers AS c
 LEFT JOIN grades AS g ON g.careers_career_id = c.career_id
 LEFT JOIN course AS crs ON crs.grades_grade_id = g.grade_id
 WHERE c.career_id = 6
 AND crs.extra_needed IS NULL

It should work, good luck.