选择t1.id，t1.name，t1.type，t2.key，t2.value from table1 t1 join table2 t2 on t1.id=t2.parent\u id where t2.key<>'active'

我认为您的查询在这种情况下不起作用，请尝试这个

SELECT name FROM table1 
 JOIN table2 ON table1.id = 
 table2.parent_id where id not IN ( SELECT parent_id 
 FROM 
table2 WHERE `key` = 'active' )

只要左连接就行了

SELECT t1.name as name
     FROM table1 as t1
LEFT JOIN table2 as t2 
       ON t1.id = t2.parent_id
      AND t2.key = 'active'
    WHERE t2.key IS NULL

Select id,name,type from table1 
where id Not in
(Select 
 parent_id from table2 
 group by parent_id 
 having key= 
 'active')

在这种情况下最好避免join，因为子查询在这种情况下会很好。

你可以用 NOT EXISTS 具体如下：

select t1.name from table1 t1 
where not exists 
(
    select 1 from table2 t2 where t1.id = t2.parent_id  AND t2.key = 'active'
)

试试这个（我想，我不完全确定我是否理解这种联系）：

SELECT t1.name as name
FROM table1 as t1
LEFT JOIN table2 as t2 ON t1.id = t2.parent_id and t2.key = 'active' where t2.key is null

一 left join 返回第一个表的所有元素，不管它们在联接表中是否有相应的记录。到那时包括 t2.key is null 在 where 子句中，将其限制为只存在于第一个表中的记录。

这将起作用：

SELECT t1.name as name
FROM table1 as t1
JOIN table2 as t2 ON t1.id = t2.parent_id
AND t2.key  not like '%active%'