Forum Stats

  • 3,826,359 Users
  • 2,260,635 Discussions
  • 7,896,916 Comments

Discussions

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

Bjornlaat
Bjornlaat Member Posts: 18 Green Ribbon
edited Jun 13, 2022 9:29AM in SQL & PL/SQL

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. 

Best Answer

  • odie_63
    odie_63 Member Posts: 8,492 Silver Trophy

    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'
    );
    

Answers

  • BluShadow
    BluShadow Member, Moderator Posts: 41,978 Red Diamond

    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
    odie_63 Member Posts: 8,492 Silver Trophy

    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
    Bjornlaat Member Posts: 18 Green Ribbon

    thanks @odie_63 that's what we wanted.


    Thankyou all for the help