values from table a that are not yet in table b

I have a php script that, inserting data in table A creates also an entry in table B. There are some cases when this script is not invoked (this is the correct behaviour) and the user may want to create the entry on table B manually.
So the user will choose an entry from table A, add some data and then create the entry in table B. I am writing the query so that the user can manually choose only between the values of table a that have not already an entry on table B (table B will look for duplicates anyway). This kind of query is an outer JOIN but seems I’m not able to make it run correctly. The query is the following:

SELECT a.id_ndg, a.name, b.id_ndg FROM 
(SELECT id_ndg, concat_ws(' ',nome, cognome) as name 
FROM anagrafiche 
WHERE substr(id_ndg,1,2)='PF' AND (nome like '%$q%' or cognome like '%$q%' or nick like '%$q%')
UNION 
SELECT id_ndg, ragsoc as name 
FROM anagrafiche 
WHERE substr(id_ndg,1,2)='PG' AND (ragsoc LIKE '%$q%' or nick like '%$q%')) as a 
LEFT OUTER JOIN
    (SELECT id_ndg FROM rischiatura WHERE id_ruolo='RU010000002') as b USING (id_ndg)

This query will always return a.id_ndg and a.name and null or value for b.id_ndg if there isn’t or there is the value in B.
I’d like this query to behave this way:

  • if b.id_ndg is null -> show the list of a.id_ndg and a.name
  • if b.id_ndg is not null -> return no result from a.

What am I doing wrong? I know I can get the same result with not in to join the two tables but I’d like to understand how to do it with outer join


Source: mysql

Leave a Reply