sub query in inner join containing another sub query

I have written a query which joins 3 tables where i need to inner join 2 tables and then inner join the whole result set with another join.

The query is as follows:

SELECT  R.ID,
        R.Title,
        R.Requirement_Text,
        R.Req_Owner,
        D.desname,
        D.team,
        D.stage,
        D.comm 
FROM Req R 
        LEFT JOIN 
        (
            SELECT  d1.ID AS 'id',
                    d1.designername AS 'desname',
                    d1.teamname AS 'team',
                    s.stage AS 'stage',
                    s.comments As 'comm' 
            FROM descomments d1 
                    LEFT JOIN stagecomments s ON d1.ID = s.ID 
                                                 AND d1.designername = s.designername
        ) D ON R.ID = D.id
WHERE R.ProjectID = '开发者_如何学编程STE 11.2' 
ORDER BY R.Priority

But i am getting following error:

com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: 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 d1.ID AS 'id',d1.designername AS 'desname',d1.teamname A

You don't need the 'descomments' after the first left join.. you're actually joining to D, which is the result of your sub-join..

SELECT 
  R.ID,
  R.Title,
  R.Requirement_Text,
  R.Req_Owner,
  D.designername,
  D.teamname,
  s.stage,
  s.comments 
FROM Req R LEFT JOIN descomments D ON R.ID=D.id
LEFT JOIN stagecomments s ON D.ID=s.ID AND D.designername=s.designername
WHERE 
  R.ProjectID='STE 11.2' 
ORDER BY R.Priority

If I have correctly stripped out the Java string delimiters and concatenation characters that appear within it, your query reads as follows:

SELECT
    R.ID,
    R.Title,
    R.Requirement_Text,
    R.Req_Owner,
    D.desname,
    D.team,
    D.stage,
    D.comm
FROM
    Req R
    LEFT JOIN descomments ON
        (
            SELECT
                d1.ID AS 'id',
                d1.designername AS 'desname',
                d1.teamname AS 'team',
                s.stage AS 'stage',
                s.comments As 'comm'
            FROM
               descomments d1
               LEFT JOIN stagecomments s ON
                   d1.ID = s.ID AND
                   d1.designername = s.designername
        ) D ON R.ID = D.id
WHERE
    R.ProjectID = 'STE 11.2'
ORDER BY
    R.Priority

The descomments ON bit after the LEFT JOIN is erroneous. Try the following:

SELECT
    R.ID,
    R.Title,
    R.Requirement_Text,
    R.Req_Owner,
    D.desname,
    D.team,
    D.stage,
    D.comm
FROM
    Req AS R
    LEFT JOIN (
        SELECT
            d1.ID AS 'id',
            d1.designername AS 'desname',
            d1.teamname AS 'team',
            s.stage AS 'stage',
            s.comments AS 'comm'
        FROM
           descomments AS d1
           LEFT JOIN stagecomments AS s ON
               d1.ID = s.ID AND
               d1.designername = s.designername
    ) AS D ON R.ID = D.id
WHERE
    R.ProjectID = 'STE 11.2'
ORDER BY
    R.Priority

I'm not sure this is the solution, but doesn't MySql use backticks ` and not '?

Like this:

SELECT  R.ID,
        R.Title,
        R.Requirement_Text,
        R.Req_Owner,
        D.desname,
        D.team,
        D.stage,
        D.comm 
FROM Req R 
        LEFT JOIN
        (
            SELECT  d1.ID AS `id`,
                    d1.designername AS `desname`,
                    d1.teamname AS `team`,
                    s.stage AS `stage`,
                    s.comments As `comm` 
            FROM descomments d1 
                    LEFT JOIN stagecomments s ON d1.ID = s.ID 
                                                 AND d1.designername = s.designername
        ) D ON R.ID = D.id
WHERE R.ProjectID = 'STE 11.2' 
ORDER BY R.Priority

Starting with StevieG's rewritten query, the values from stageComments are never used in the query - so it appears to be redundant - but will slow down the query significantly; the following should give the desired results much quicker:

SELECT 
  R.ID,
  R.Title,
  R.Requirement_Text,
  R.Req_Owner,
  D.desname,
  D.team,
  D.stage,
  D.comm 
FROM Req R LEFT JOIN descomments D ON R.ID=D.id
WHERE 
  R.ProjectID='STE 11.2' 
ORDER BY R.Priority;