mysql - 与左表中的最后一条插入记录和右表中的所有记录进行左连接

Question

根据公司ID从左表中使用连接与最后插入记录获取数据，右表中的所有记录并不重要，左表中有任何匹配记录.

**company_list**
id
company_id
company_name
company_address1
company_address2
company_phone
company_headoffice
active

**company_subscription_list** table field name is 
id
company_id
company_name
company_address1
company_address2
company_phone
company_headoffice
active

我正在使用此查询来获取 fecth 记录

SELECT *, c.id AS main_id
FROM company_list as c
LEFT JOIN company_subscription_list as s ON c.company_id = s.company_id
where s.id IN (select max(id) from company_subscription_list GROUP BY company_id)

但它没有显示那些尚未订阅的公司的结果。

需要帮助。

Answer 1

您必须将您的条件放入连接条件中。这样做，您将不会丢失线条:

SELECT *, c.id AS main_id 
FROM company_list as c 
LEFT JOIN company_subscription_list as s 
    ON c.company_id = s.company_id 
    AND s.id IN (select max(id) from company_subscription_list GROUP BY company_id)

尝试此链接:http://sqlfiddle.com/#!9/5db87/4