SQL Help

[rkeokitv]

I have three tables with their following attributes:

INVOICE - rowstamp, ponumber, activitynumber, invoiceno
PO - ponumber, postart, poend
ACTIVITY - code

PO, which has a list of Purchase Order Numbers; ACTIVITY, which has a list of Activities, and INVOICE, which matches activities to POs. Some Activities and POs are matched more than once - this is because there may be multiple invoices to the PO on the same activity; however, some POs are charged to more than one Activity, based on line items. Invoice number isn't really relevant to this query.

ACTIVITY is joined to INVOICE on the activitynumber field. PO is joined to INVOICE on the ponumber field. There are no primary keys in any of the tables.

What I'm trying to do is create a query between INVOICE, ACTIVITY, and PO that will list only those PO numbers (ponumber) that are associated with ALL codes (code) in the ACTIVITY table.

[abhaysk]

Select PO.* 
from INVOICE I, PO, ACTIVITY A 
where I.ponumber        =    PO.ponumber       AND
      I.activitynumber  =    A.code            AND
      A.code            =    'ALL'
;

Is this you want?

Abhay.

[TomazZ]

Originally posted by abhaysk

      A.code            =    'ALL'

I like this approach. It's fast and simple.

What about

select po.ponumber
  from po
 where not exists (select code from activity
                    minus
                   select activitynumber
                     from invoice i
                    where i.ponumber = po.ponumber)

[rkeokitv]

select po.ponumber
from po
where not exists (select code from activity
minus
select activitynumber
from invoice i
where i.ponumber = po.ponumber)
-----

That worked....