i have two tables NAMES and NAMES_VERIFICATIONS

NAMES

id     fname    Tax  
1      jack     56982
1000   Tim      32165
2321   Andrew   98956
231    Jim      11215

NAMES_VERIFICATIONS

id     idtype  iddata
1      tax      56982
1      passport 12365
2321   tax      98956
2321   passport 65656

so if you notice there is no ID 1000 in the NAMES_VERIFICATIONS table
so i want my result to be something like this

Expected output

NAMES.id   NAMES.fname  NAMES.TAX   NAMES_VERIFICATIONS.iddata
1          jack            56982     56982     
1000       Tim            32165      NULL
..
...

things i have tried was using left join like this

Select Names.id,Names.fname,NAMES.TAX,NAMES_VERIFICATIONS.iddata
FROM Names
LEFT JOIN NAMES_VERIFICATIONS 
ON Names.id = NAMES_VERIFICATIONS.id
WHERE NAMES.VERIFICATION.idtype = 'tax'
Order by Names.id

but the result i get is

output

NAMES.id   NAMES.fname  NAMES.TAX   NAMES_VERIFICATIONS.iddata
1          jack            56982     56982     
2321       Andrew          98956     98956
..
...

but i want it to also display IDS which are not present in NAMES_VERIFICATIONS

how do i go about doing this?

>Solution :

Move the criteria in the WHERE clause to the ON clause of the join:

SELECT n.id, n.fname, n.TAX, nv.iddata
FROM Names n
LEFT JOIN NAMES_VERIFICATIONS nv
    ON n.id = nv.id AND nv.idtype = 'tax'
ORDER BY n.id;