title: MySQL view from three tables
body: <p>I've read bunches of posts, books, watched videos, etc. etc. but SQL JOINS just have never made any sense to me... =/</p>
  
  <p>So here's the deal...</p>
  
  <p>Three tables:</p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS `feature` (  
    `feature_id` INT NOT NULL AUTO_INCREMENT ,  
    `title` VARCHAR(45) NOT NULL ,  
    `description` TEXT NOT NULL ,  
    PRIMARY KEY (`feature_id`) )  
  ENGINE = InnoDB;  
  
  CREATE TABLE IF NOT EXISTS `requirement` (  
    `requirement_id` INT NOT NULL AUTO_INCREMENT ,  
    `title` VARCHAR(45) NOT NULL ,  
    `description` TEXT NOT NULL ,  
    PRIMARY KEY (`requirement_id`) ,  
    UNIQUE INDEX `title_UNIQUE` (`title` ASC) )  
  ENGINE = InnoDB;  
  
  CREATE TABLE IF NOT EXISTS `feature_requirement` (  
    `fk_feature_id` INT NOT NULL ,  
    `fk_requirement_id` INT NOT NULL ,  
    PRIMARY KEY (`fk_feature_id`, `fk_requirement_id`) ,  
    INDEX `fk_feature_requirement_requirement1` (`fk_requirement_id` ASC) ,  
    INDEX `fk_feature_requirement_feature1` (`fk_feature_id` ASC) )  
  ENGINE = InnoDB;  
  </code></pre>
  
  <p>Some features may have multiple requirements and some features will have the same requirements (that is, the same requirement applies to multiple features) Hence, the third table for a many-to-may relationship.</p>
  
  <p>I want to be able to select all the requirements for a particular feature (by feature_id) and I'm trying to make this into a VIEW.</p>
  
  <p>After a lot of error messages about "unknown columns" and "non unique aliases;" I finally got this one to work:</p>
  
  <pre><code>CREATE VIEW `project_feature_requirement` AS  
  SELECT r.*  
  FROM `requirement` `r`  
  INNER JOIN `feature_requirement` `fr`  
      on `r`.`requirement_id` = `fr`.`fk_requirement_id`  
  INNER JOIN `feature` `f`  
      on `fr`.`fk_feature_id` = `f`.`feature_id`  
  </code></pre>
  
  <p>However, that only gives me requirement_id, title, &amp; description to SELECT from though.
  ...which is useless</p>
  
  <p>I have four other VIEWs to create that will be the same type of query.</p>
  
  <p>Any help would be greatly appreciated!</p>
  
  <p>Are there any free programs that will query my schema and run me through a "wizard" or something to specify which tables/columns I want and what column(s) I want to select on and build the query for me???  Maybe I could finally figure it out if could play with a utility like that.</p>
  
  <p>Thanks again!
  Todd</p>