MySQL /PHP-2个表的不同参数

Question

我有以下SQL：

$queryString = "
            SELECT     
                iR.lastModified,
                d.*,
                c2.title as stakeholderTitle,
                u.username as authorUsername,
                c.title as authorContactName,  
                GROUP_CONCAT(iR.stakeholderRef) AS participants
            FROM
                informationRelationships iR,
                contacts c2
            INNER JOIN
                debriefs d ON
                d.id = iR.linkId 
            LEFT JOIN
                users u ON
                u.id = iR.author      
            LEFT JOIN
                contacts c ON
                c.ref = u.contactId
            LEFT JOIN 
                debriefs d2 ON
                d2.stakeholder = c2.ref                
            WHERE               
                ( 
                    iR.clientRef = '$clientRef' OR  
                    iR.contactRef = '$contactRef'   
                )
                AND    
                    iR.projectRef = '$projectRef' AND
                    iR.type = 'Debrief' 
            GROUP BY
                iR.linkId
            ORDER BY
                d.dateOfEngagement                    
        ";     

请注意，对于contacts表，我需要2位不同的数据。

所以在某一时刻，我需要匹配

c.ref = u.contactId 

这将返回一段信息。

但我也需要一个完全不同的分组：

d2.stakeholder = c2.ref

问题是，标题是我对这两个专栏感兴趣的专栏：

c2.title as stakeholderTitle,
...
c.title as authorContactName

我该怎么做呢？

我现在的尝试是：

Error: Unknown column 'iR.linkId' in 'on clause'

​

​

我不确定我真的明白这里发生了什么：

how to join two tables on common attributes in mysql and php?

EDIT::::---ANSWERED--zerkms

    $queryString = "
        SELECT     
            iR.lastModified,
            d.*,
            c2.title as stakeholderTitle,
            u.username as authorUsername,
            c.title as authorContactName,  
            GROUP_CONCAT(iR.stakeholderRef) AS participants
        FROM
            informationRelationships iR            
        INNER JOIN
            debriefs d ON
            d.id = iR.linkId  
        INNER JOIN 
            contacts c2 ON
            d.stakeholder = c2.ref  
        LEFT JOIN
            users u ON
            u.id = iR.author      
        LEFT JOIN
            contacts c ON
            c.ref = u.contactId             
        WHERE               
            ( 
                iR.clientRef = '$clientRef' OR  
                iR.contactRef = '$contactRef'   
            )
            AND    
                iR.projectRef = '$projectRef' AND
                iR.type = 'Debrief' 
        GROUP BY
            iR.linkId
        ORDER BY
            d.dateOfEngagement                    
    ";     

通过重新排序我的查询，我设法在.谢了，泽克！

Answer 1

在mysql中，不能将隐式联接和显式联接混合在一个查询中。

所以

FROM informationRelationships iR,
     contacts c2

应该重写为

FROM informationRelationships iR
INNER JOIN contacts c2 ON ...

Answer 2

不要使用笛卡尔乘积和在同一个查询中加入(而不是子查询)，在这里，只使用联接(交叉连接与笛卡尔乘积相同)。