mysql - SQL Join，不确定复杂查询中的确切语法

Question

我正在尝试匹配两位数据，添加到现有查询中。

我希望查询本身能解释我正在尝试做的事情。我遇到麻烦的行是 LEFT JOIN practitioners p ON insolvencies.practitioner = practitioners.id,

SELECT DISTINCT     i.id            AS  id,
                    i.company       AS  company,
                    i.insolvencyDate AS insolvency_date,
                    i.city          AS  city,
                    i.createdOn     AS  createdOn,
                    1               AS  rank_id,
                    t.entryCopy     AS  insolvency_type,
                    i.businessNature AS business_nature,
                    p.name          AS pract_comp_name,
                    'I'             AS  i_type 
    FROM     insolvencies i, 
        LEFT JOIN practitioners p ON insolvencies.practitioner = practitioners.id, 
        lookupcopy t 
    WHERE    i.checked   =  1    
        AND t.id     =  i.insolvencyType 
        AND i.insolvencyDate    >=  {ts '2010-01-22 00:00:00'} 
        AND i.insolvencyDate    <=  {ts '2011-10-20 00:00:00'} 
        AND (LOWER(p.name)   LIKE   '%Hodgsons%') 
    GROUP BY     id

Answer 1

这里有很多问题。首先，我去掉了逗号，并正确地加入了 lookupcopy。我不能 100% 确定您的最终目标，但可以从以下几点着手:

SELECT DISTINCT i.id AS id,  
                i.company AS company,  
                i.insolvencyDate AS insolvency_date,  
                i.city AS   city,  
                i.createdOn AS createdOn,  
                1 AS rank_id,  
                t.entryCopy AS insolvency_type,  
                i.businessNature AS business_nature,  
                p.name AS pract_comp_name,  
                'I' AS i_type  
    FROM insolvencies i
        INNER JOIN lookupcopy t  on (t.id = i.insolvencyType)
        LEFT JOIN practitioners p ON i.practitioner = p.id
    WHERE i.checked = 1   
        AND i.insolvencyDate >= {ts '2010-01-22 00:00:00'}  
        AND i.insolvencyDate <= {ts '2011-10-20 00:00:00'}  
        AND (LOWER(p.name)   LIKE '%Hodgsons%')  
    GROUP BY id 

如果您仍然遇到问题，请发布您的实际错误。

另外，还有一件事:在 where 子句中引用 p.Name 将导致从业者和破产者之间的内部连接。这是故意的吗？