RE: SQL help

OK, let's try it again...

Does this work:

with temp1 as (select a1 from BUS a inner join CONT b on a.a1 = b.a1 inner
join OCCU c on b.c1 = c.c1),
temp2 as (select * from BUS a inner join CONT b on a.a1 = b.a1 exception
join OCCU c on b.c1 = c.c1)
select * from temp2 exception join temp1 on temp1.a1 = temp2.a1

temp1 will get you all records from BUS where there is a matching record in
OCCU. Temp2 will get you everything in BUS where there is not a match in
OCCU. If I understand correctly, you want everything in temp2 that does not
exist in temp1.

JJ


-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of Bob Bledsoe
Sent: Thursday, February 08, 2007 4:08 PM
To: RPG programming on the AS400 / iSeries
Subject: Re: SQL help

I tried this but if the BUS record has 2 CONT records and one of these has a
record in OCCU and one doesn't if give me the BUS record. I don't want the
BUS record if there is any connection to OCCU. Am I making any since?



On 2/8/07, John Joiner <john@xxxxxxxxxxxxxx> wrote:

I think this will get you what you want (not tested)...

select a.* from BUS a inner join CONT b on a.b1 = b.b1 exception join OCCU
c
on b.c1 = c.c1

JJ



-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of Bob Bledsoe
Sent: Thursday, February 08, 2007 3:36 PM
To: rpg400-l@xxxxxxxxxxxx
Subject: SQL help

I have 3 files, BUS, CONT, OCCU. BUS is tied to CONT by field b1 and there
can be more the one record in CONT for each record in BUS. CONT is tied to
OCCU by field C1. What I am trying to get is all the records ib BUSS that
have NO record in OCCU. I'm totally lost as to how to do this with SQL.
--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.




--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.