SQLite - count the primary keys that were not referenced as foreign keys

Navigation
3
  • First, I am trying to count the primary keys of a table that were NOT referenced as foreign keys in another table.
  • In addition, I want to filter the search by using the LIKE clause to display only those containing a certain letter of a given attribute.

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

I'm 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 the foreign keys that were not referenced and without the LIKE clause. Please, if anyone can give me an example of QUERY to do this search, I appreciate it.

    
asked by anonymous 02.04.2014 / 22:30

1 answer

2

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
    
02.04.2014 / 22:44
Divs being added one on top of the other PHPUnit and Selenium - How to call specific functions of the test script?