Need help for an SQL statement, where we want to see all shipments where no rooskens_enity is presen

Bjornlaat

We want to create a query that checks whether a shipment has a Rooskens enity. 

The queries should show all shipments that do not have a Rooskens entity. To create this queries we need to use 2 tables namely: 

SHIPMENT and SHIPMENT_REFNUM

The difficulty for us is that in SHIPMENT_REFNUM are about 10 records of 1 shipment (see example data) 

TABLE (SHIPMENT)

Example data: (fieldname SHIPMENT_GID)

SHIPMENT_123

SHIPMENT_1234

table (SHIPMENT_REFNUM)

Sample data (field names: SHIPMENT_GID, SHIPMENT_REFNUM_QUAL_GID)

SHIPMENT_123, ROOSKENS_ENTITY

SHIPMENT_123, GLOG

SHIPMENT_123, PAYMENT_TERM

SHIPMENT_123, LOADING_METERS

SHIPMENT_123, LICENSE_PLATE

SHIPMENT_123, REVENUE_PERCENTAGE

SHIPMENT_1234, GLOG

SHIPMENT_1234, PAYMENT_TERM

SHIPMENT_1234, LOADING_METERS

SHIPMENT_1234, LICENSE_PLATE

SHIPMENT_1234, REVENUE_PERCENTAGE

Based on this data it is visible that SHIPMENT_GID has no ROOSKENS_ENITY. We want to get this shipment back. It is important for the program that the select statement only contains SHIPMENT_GID from the table SHIPMENT. 

I would like your help with this. 

Thanks in advance. 

odie_63

Unless I'm missing something, a simple (NOT) EXISTS predicate should do just fine :

select sh.shipment_gid
from shipment sh
where not exists (
 select null
 from shipment_refnum sr
 where sr.shipment_gid = sh.shipment_gid
 and sr.shipment_refnum_qual_gid = 'ROOSKENS_ENTITY'
);

BluShadow

Here's one way.

SQL> with shipment(SHIPMENT_GID) as (
  2    select 'SHIPMENT_123' from dual union all
  3    select 'SHIPMENT_1234' from dual
  4    )
  5    ,shipment_refnum(shipment_gid, shipment_refnum_qual_gid) as (
  6    select 'SHIPMENT_123', 'ROOSKENS_ENTITY' from dual union all
  7    select 'SHIPMENT_123', 'GLOG' from dual union all
  8    select 'SHIPMENT_123', 'PAYMENT_TERM' from dual union all
  9    select 'SHIPMENT_123', 'LOADING_METERS' from dual union all
 10    select 'SHIPMENT_123', 'LICENSE_PLATE' from dual union all
 11    select 'SHIPMENT_123', 'REVENUE_PERCENTAGE' from dual union all
 12    select 'SHIPMENT_1234', 'GLOG' from dual union all
 13    select 'SHIPMENT_1234', 'PAYMENT_TERM' from dual union all
 14    select 'SHIPMENT_1234', 'LOADING_METERS' from dual union all
 15    select 'SHIPMENT_1234', 'LICENSE_PLATE' from dual union all
 16    select 'SHIPMENT_1234', 'REVENUE_PERCENTAGE' from dual
 17    )
 18  select shipment_gid
 19  from   shipment s
 20  minus
 21  select shipment_gid
 22  from   shipment_refnum
 23  where  shipment_refnum_qual_gid = 'ROOSKENS_ENTITY'
 24  /

SHIPMENT_GID
-------------
SHIPMENT_1234

If that's not what you wanted, then please post details of your expected results. You should also specify your database version as different versions have different capabilities.

And it helps us if you post your data in a format we can create easily on our own databases, so that we can easily help you, rather than each person having to try and take your question and format your data into usable tables.

odie_63

Unless I'm missing something, a simple (NOT) EXISTS predicate should do just fine :

select sh.shipment_gid
from shipment sh
where not exists (
 select null
 from shipment_refnum sr
 where sr.shipment_gid = sh.shipment_gid
 and sr.shipment_refnum_qual_gid = 'ROOSKENS_ENTITY'
);

Bjornlaat

thanks @odie_63 that's what we wanted.

Thankyou all for the help