Qfyilyi

I have 2 tables

id      name   type

1       aa     driver

2       bb     cyclist

3       cc     runner



parent_id      key      value

1              mobile   00299029

2              mobile   008772

2              active   1

3              mobile   09887

3              active   0

I need to get the record 1,aa,driver, the one that has not a record with value 'active' in the second table.
My last try was something like this, but I'n not sure to be even a bit close to what I need, my result is always 0 records

SELECT t1.name as name

FROM table1 as t1

JOIN table2 as t2 ON t1.id = t2.parent_id

AND NOT EXISTS (

  SELECT * FROM table2

   WHERE key = 'active'

)

Just a left join would do

    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

Try this (I think, not entirely sure I understand the linkage):

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

A left join returns all elements of the first table regardless of whether they have a corresponding record in the joined table. By then including t2.key is null in the where clause, you limit it to records that only exist in the first table.

This will work:

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%'

You can use NOT EXISTS as follows:

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 id,name,type from table1 

where id Not in

(Select 

 parent_id from table2 

 group by parent_id 

 having key= 

 'active') 

Better avoid join in this case I would say as subquery
would do good in this case.

I think Your query is not working in this case, please try
this

 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.id,t1.name,t1.type,t2.key,t2.value
FROM table1 t1
JOIN table2 t2 ON t1.id = t2.parent_id
where t2.key <>'active'