mysql查询帮助，从另一个表中使用关系id获取表中的值

THE SQL THAT BUILDS THE TABLES,

建造表格的SQL,

    --
-- 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.

例如,用户想成为一名电工,他们在学校有2个D级,这意味着他们只能完成2级课程,这意味着要完成课程,他们必须做更高级别的课程。我需要能够根据他们选择电工和2级课程的事实来展示其他课程的内容,值得注意的是,需要额外工作的课程有一个字段'extra_needed`,标记为是。

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.

然而,这会带回59行数据,因为它应该带回2行数据,另一行带回用户可以选择其他等级选择的数据行。

2 个解决方案

#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:

因此,对于与career.career_id = 6相关的所有课程,查询将如下所示:

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:

您需要一个更复杂的查询来执行您最初的问题,但这不仅需要指定career_id,还需要指定course_id,然后是条件语句,说明是否需要进一步的课程但是我不确定您是否拥有所有这样做的领域,因为你需要知道他们选择的课程与所有其他相关职业课程之间的关系。如果您只是希望看到与该职业相关的所有其他课程,那么您将添加如下行:

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

#2

Replace your query by this one:

用以下内容替换您的查询:

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

它应该工作,祝你好运

#1