Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

joining two tables eventhough the id does not display on the second table

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

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

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;
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading