title: nested subquery, SQL
body: <p>I am trying to write a nested subquery where I choose 2 columns from 2 different tables which satisfies 3 conditions. I also insert the 2 columns into another table which actually takes 3 input but 3rd one is default. </p>
  
  <p>I tried to use nested subquery but I get error all the time and I couldn't solve it. Is there another way that I can do it such as natural join.   </p>
  
  <pre><code>INSERT INTO agency_response (AgencyName,RequestID) VALUES(AgencyName,RequestID)
  SELECT AgencyName, RequestID
  FROM ScoutingAgency, Request
  WHERE AgencyName = @scouting_agency_name_input AND 
      RequestID = (SELECT RequestID, 
      FROM Request, Club
      WHERE RequestName = @request_name_input AND ClubName = @club_name_input);
  
  
  CREATE TABLE ScoutingAgency(
      AgencyEmail VARCHAR(64) UNIQUE,
      AgencyName VARCHAR(64) NOT NULL,
      NumberOfScouts INT,
      PRIMARY KEY(AgencyName),
      FOREIGN KEY(AgencyEmail) REFERENCES User(UserEmail) ON DELETE SET NULL
  );
  
  
  CREATE TABLE Club(
      ClubEmail VARCHAR(64),
      ClubName VARCHAR(64),
      League VARCHAR(64),
      ClubRank INT,
      Country VARCHAR(64),
      Organization VARCHAR(64),
      PRIMARY KEY(ClubName),
      FOREIGN KEY(ClubEmail) REFERENCES User(UserEmail) ON DELETE SET NULL
  );
  
  
  CREATE TABLE Request(
      RequestID INT AUTO_INCREMENT,
      RequestName VARCHAR(64),
      Organization VARCHAR(64),
      NumberOfScouts INT,
      PlayerPosition VARCHAR(64),
      RequestStatus VARCHAR(64),
      PRIMARY KEY(RequestID)
  );
  
  
  CREATE TABLE agency_response(
      AgencyName VARCHAR(64),
      RequestID INT,
      Answer VARCHAR(64) DEFAULT('waiting for response'),
      PRIMARY KEY(AgencyName,RequestID),
      FOREIGN KEY(AgencyName) REFERENCES ScoutingAgency(AgencyName) ON DELETE CASCADE,
      FOREIGN KEY(RequestID) REFERENCES Request(RequestID) ON DELETE CASCADE
  );
  </code></pre>
  
  <blockquote>
    <p>ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT AgencyName, RequestID FROM ScoutingAgency, Request WHERE ' at line 2</p>
  </blockquote>