如何创建这个复杂的查询？

Question

我有三个表:项目，技能和project_skills。在projects表中，我保存了项目的一般数据。第二个表格技能，我持有技能id和技能名称，我也有projects_skills表，这是持有项目的技能关系。以下是表格计划：

CREATE TABLE IF NOT EXISTS `project_skills` (
  `project_id` int(11) NOT NULL,
  `skill_id` int(11) NOT NULL,
  KEY `project_id` (`project_id`),
  KEY `skill_id` (`skill_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_turkish_ci;


CREATE TABLE IF NOT EXISTS `projects` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `employer_id` int(11) NOT NULL,
  `project_title` varchar(100) COLLATE utf8_turkish_ci NOT NULL,
  `project_description` text COLLATE utf8_turkish_ci NOT NULL,
  `project_budget` int(11) NOT NULL,
  `project_allowedtime` int(11) NOT NULL,
  `project_deadline` datetime NOT NULL,
  `total_bids` int(11) NOT NULL,
  `average_bid` int(11) NOT NULL,
  `created` datetime NOT NULL,
  `active` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `created` (`created`),
  KEY `employer_id` (`employer_id`),
  KEY `active` (`active`),
  FULLTEXT KEY `project_title` (`project_title`,`project_description`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_turkish_ci AUTO_INCREMENT=3 ;

CREATE TABLE IF NOT EXISTS `skills` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `category` int(11) NOT NULL,
  `name` varchar(100) COLLATE utf8_turkish_ci NOT NULL,
  `seo_name` varchar(100) COLLATE utf8_turkish_ci NOT NULL,
  `total_projects` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `seo_name` (`seo_name`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_turkish_ci AUTO_INCREMENT=224 ;

我想选择具有相关技能名称的项目。我想我必须使用联接，但我不知道该怎么做。谢谢

Answer 1

听起来像一个JOIN是完全正确的：

SELECT ...
FROM projects
INNER JOIN project_skills ON (project_skills.project_id = projects.id)
INNER JOIN skills ON (skills.id = project_skills.skill_id)

Answer 2

select * from projects
left join project_skills on projects.id = project_skills.project_id
left join skills on project_skills.skills_id = skills .id

注意:您不需要所有的列，但是这将让您在选择所需的列之前看到发生了什么。