select statement from two tables

[sandy1028]

I have to 3 tables Table1,table2,table3 and in all tables field name  `fieldname` is similar .

 

I have to select values from table1 the values which is not in table2 and table3.

 

Select  distinct(fieldname) from table1 where fieldname not in (select distinct(fieldname) from table2 union (select distinct(fieldname) from table3)

 

 

But I am gtting the proper result

[lenerd3000]

im not sure about this but i think it worth a try...

 

Select  distinct(fieldname) from table1 where fieldname not exists (select distinct(fieldname) from table2 and not exists (select distinct(fieldname) from table3)

[fenway]

A LEFT JOIN on the union'ed table could work.

[aschk]

I'm curious to know the database layout as it would seem that the tables aren't normalised at first glance.

 

SELECT t1.fieldname 
FROM table1 t1
LEFT JOIN table2 t2 ON t1.fieldname = t2.fieldname
LEFT JOIN table3 t3 ON t1.fieldname = t3.fieldname
WHERE t2.fieldname IS NULL
OR t3.fieldname IS NULL