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