RE: Yet another SQL newbie question

James,

If INTERSECT means to select records from one file that have matching
records in another file, you can use an INNER JOIN.

SELECT A.* FROM FILE1
INNER JOIN FILE2 B ON (A.FIELD1=B.FIELD1 AND A.FIELD2=B.FIELD2 ...)

That will select all the records from FILE1 that have a matching record in
FILE2.

If EXCEPT means to select records from one file that do NOT have matching
record in another file, you can use an EXCEPTION JOIN.

SELECT A.* FROM FILE1
EXCEPTION JOIN FILE2 B ON (A.FIELD1=B.FIELD1 AND A.FIELD2=B.FIELD2 ...)

That will select all the records from FILE1 that do NOT have a matching
record in FILE2.

Hope this helps!
Richard

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of James H H Lampert
Sent: Wednesday, November 23, 2005 1:37 PM
To: midrange-l@xxxxxxxxxxxx
Subject: Yet another SQL newbie question


My fellow geeks:

My SQL project is progressing nicely, but I've run into
yet another place where I'm at a bit of a loss.

I need to be able to UNION or INTERSECT an arbitrary
number of identically-structured files, and I need to, at
times, EXCEPT records from that intersection or union,
based on the records in yet another identically-structured
file.

I note that AS/400 SQL, at least at V4, has a UNION, but
not an INTERSECT or an EXCEPT. Can anybody suggest where I
should be looking for an alternate way to do this?

--
JHHL