You don´t need to use "NOT IN" for solving this problem.
Just do a normal join operation (i.e. check the conditions)

Regards
/ Ali


-----Ursprungligt meddelande-----
Från: Niall Hannon (ext. 772) [mailto:Niall.Hannon@f...]
Skickat: den 11 juli 2002 18:28
Till: sql language
Ämne: [sql_language] This is IMPOSSIBLE!


Hi,

Heres a sql problem that I dont think is possible but I am open to being
corrected.

I have 2 tables, tableA and tableB. Pretty simple so far!

TableA:
ID
Project
Role
Days

TableB:
ID
Project
Role
Days

So both tables have same design but different data.

I need to return, using SQL, all the records in TableA plus all the records
in TableB that do not appear in TableA. Here is the tricky part - I need to
look at 2 fields when checking if the records in TableB exist in TableA. i.e
Project and Role fields.

Check the record and look at the Project and Role field values. If they
exist in TableA for the record then ignore it, otherwise I need it returned
in the SQL set. This is easy when looking at just 1 field -

	"Select DAYS from TableA union select DAYS from TableB where project
not in(select project from TableA)"

But how do you do this when I want to say something like.....where project
and Role not in(select project from TableA)? Its not possible!!!!!

Thanks
Niall


**************************************************************************
The information contained in this e-mail is confidential,
may be privileged and is intended only for the use of the
recipient named above. If you are not the intended
recipient or a representative of the intended recipient,
you have received this e-mail in error and must not copy,
use or disclose the contents of this email to anybody
else. If you have received this e-mail in error, please
notify the sender immediately by return e-mail and
permanently delete the copy you received. This email has
been swept for computer viruses. However, you should
carry out your own virus checks.


Registered in Ireland, No. 205721. http://www.FINEOS.com
**************************************************************************