Hi Niall,

Try the following --

SELECT Days FROM TableA
UNION
SELECT Days FROM TableB B WHERE NOT EXISTS (SELECT ID FROM TableA A WHERE
B.Project = A.Project 
AND B.Role = A.Role)

HTH,
Ayyappan




-----Original Message-----
From: Niall Hannon (ext. 772) [mailto:Niall.Hannon@f...]
Sent: Thursday, July 11, 2002 12:48 PM
To: sql language
Subject: [sql_language] RE: This is IMPOSSIBLE!


Hi,

But this cant work. 

Lets say data in TableA Is

ID	Project	Role	Days
1	Rollout	DBA	2
2	Rollout	STA	3
3	Test		TTT	2

tableb

ID	Project	Role	Days
1	Rollout	DBA	2
3	Rollout	TTT	3
4	Test		TTT	2

Your Statement would return no records from TableB - it should bring Record
ID 3 from TableB. Because I need to compare Project AND Role per reocord.

-----Original Message-----
From: Denis Gobo [mailto:dgobo@w...]
Sent: 11 July 2002 17:41
To: sql language
Subject: [sql_language] RE: This is IMPOSSIBLE!



select DAYS 
from TableB 
where project not in(select project from TableA)
And	role not in(select role from TableA)

-----Original Message-----
From: Niall Hannon (ext. 772) [mailto:Niall.Hannon@f...] 
Sent: Thursday, July 11, 2002 12:28 PM
To: sql language
Subject: [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
************************************************************************
**







"The information in this email, and in any attachments,
may contain confidential information and is intended 
solely for the attention and use of the named addressee(s).
It must not be disclosed to any person without authorization.
If you are not the intended recipient, or a person responsible for
delivering it to the intended recipient, you are not authorized
to, and must not, disclose, copy, distribute, or retain this
message or any part of it."