THE SQL THAT Develops 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 introduction to my concept of the tables, is the fact that each grade is connected having a career and something career might have many grades, from that certain course is just connected to 1 course, but a person might need to do an additional course when the one they pick isn't accredited highly enough.

So my real question is how do you choose the course particulars for that greater level courses when the user chooses a minimal level course,

for instance the consumer really wants to be an electrical installer, and they've 2 D grades in class what this means is they are able to only perform a level 2 course, which means that to accomplish the course they need to perform a greater level course. I have to have the ability to show exactly what the other courses derive from the very fact they've selected electrical installer and an amount 2 course, it's worth observing that courses that need work possess a area 'extra_needed` that's marked as yes.

I am unable to for that live or me see how to obtain the right data out, I've attempted the next,

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 information while it will restore 2 rows of information, another to rows of information the user could choose when they find the other grade options.

Looks in my experience as if you are joining around the wrong fields, the associations seem like they'd be the following:

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

so for those courses associated with career.career_id = 6 the query would look the following:

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'd require a more complicated query to complete that which you initially requested though which may involve indicating not just a career_id but additionally a training course_id after which a conditional statement to express whether any more classes are needed but I am unsure for those who have all of the fields necessary to get this done as you should be aware of relationship between your course they've selected and all sorts of other courses relating towards the relevant career. Should you simply desire to see the rest of the courses relevant to that particular career you would then give a line like:

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

If you will find only ever two amounts of courses then you may give a line like below as should they have selected the greater level it cannot satisfy both last statement and that one whereas should they have selected the low level both is going to be true:

and course.extra_needed IS NULL

Replace your query by that one:


Choose *

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 will work, Best of luck