comparision of two table having same field?

Question

hi all, I have to table. Both table has nric field.

I want to select the nric field which does not inside in table.

si_isccourse table enter code here

ID NRIC
1  456
2  457
3  458 

si_results table

ID   NRIC
1    456

si_isc_class table

ID NRIC
1  456
2  457

my results like this

ID NRIC
3  458

this is my sql query

SELECT DISTINCT(isc.isc_nric) from si_isccourse iscLEFT JOIN si_results re ON re.re_nric=isc.isc_nric LEFT JOIN si_isc_class cla ON isc.isc_nric!=cla.isc_class_nric WHERE (isc.isc_second_choice='FPS') AND ( re.re_year IN('2010','2009')) AND ( re.re_code IN('VETCA1','VETCA2')) AND isc.isc_nric!=cla.isc_class_nric ORDER BY re.re_mark desc

I want to get data not in si_isc_class table. I want to select data from si_isccourse and compare with si_results and not in si_isc_class

Answer 1

SELECT first.*
FROM first
LEFT JOIN second ON(first.id = second.id)
WHERE second.id IS NULL

Answer 2

You might want to change the position of the tables.

some thing like

...
FROM si_isc_class ..
Left JOIN ....

Then you will get the desired value.