Sqlite - count primary keys that were not referenced as foreign key

Question

• First, I am trying to count the primary keys of a table that WERE and WERE NOT referenced as a foreign key in another table.
• Also, I want to filter the search using the clause LIKE to show only those containing a certain letter of a given attribute.

See the explanation in the image below (original here):

I am using this QUERY:

SELECT l.identifier AS id, l.name AS listName, COUNT(p.list_identifier) AS regCount 
FROM list AS l 
LEFT OUTER JOIN person AS p ON l.identifier = p.list_identifier
WHERE p.list_identifier IS NULL
GROUP BY l.name

However, it counts foreign keys that have not been referenced and without the LIKE clause. Please, if anyone can give me an example of QUERY that does this search, I thank you.

Answer 1

Try:

SELECT l.identifier AS id, l.name AS listName, 
 CASE COUNT(coalesce(p.list_identifier,0)) WHEN 0 THEN 'Não referenciado' ELSE 'Referenciado' END AS Situacao
 FROM list AS l LEFT OUTER JOIN person AS p ON l.identifier = p.list_identifier
  WHERE atributo LIKE '%l%'
   GROUP BY l.name

Sorry, I put the coalesce in the wrong position.

SELECT l.identifier AS id, l.name AS listName, 
 CASE coalesce(COUNT(p.list_identifier,0)) WHEN 0 THEN 'Não referenciado' ELSE 'Referenciado' END AS Situacao
 FROM list AS l LEFT OUTER JOIN person AS p ON l.identifier = p.list_identifier
  WHERE atributo LIKE '%l%'
   GROUP BY l.name