mysql query help, getting values from table using relational id's from another table

Question

THE SQL THAT BUILDS THE 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 the tables, is that each grade is associated with a career and one career can have many grades, from that one course is only associated to one course, but a user may need to do an extra course if the one they pick is not accredited highly enough.

So my question is how do I select the course details for the higher level courses if the user selects a low level course,

for example the user wants to be an electrician, and they have 2 D grades in school this means they can only do a level 2 course, this means that to complete the course they have to do a higher level course. I need to be able to show what the other courses are based on the fact they have selected electrician and a level 2 course, it is worth noting that courses that require extra work have a field 'extra_needed` that is marked as yes.

I cannot for the live or me work out how to get the right data out, I have 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 brings back 59 rows of data where as it should bring back 2 rows of data, the other to rows of data that the user could select if they chose the other grade choices.

Answer 1

Looks to me like you are joining on the wrong fields, the relationships look like they would be as follows:

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

so for all courses related to career.career_id = 6 the query would look as follows:

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 would need a more complex query to do what you originally asked though which would involve specifying not only a career_id but also a course_id and then a conditional statement to say whether any further courses are required but I'm not sure if you have all the fields necessary to do this as you would need to know the relationship between the course they have selected and all other courses pertaining to the relevant career. If you simply wish to see all the other courses relating to that career then you would add a line like:

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

If there are only ever two levels of courses then you could add a line like below as if they have selected the higher level it can't satisfy both the last statement and this one whereas if they have selected the lower level both will be true:

and course.extra_needed IS NULL