show all locations that do not meet certain requirements

Bjornlaat

shows all location_gid which have no CUSTOMER

LOCATION table sample data:

LOCATION_GID

1

2

3

4

5

LOCATION_ROLE_PROFILE table sample data

LOCATION_GID, LOCATION_ROLE_GID

1, CUSTOMER

1, SHIPFROM/SHIPTO

2, SHIPFROM/SHIPTO

3, CUSTOMER

3, SHIPFROM/SHIPTO

4, CUSTOMER

4, SHIPFROM/SHIPTO

5, CARRIER

We want to create an SQL query of all location_gid of the table location that do not have a CUSTOMER in the table LOCATION_ROLE_PROFILE

As you can see in the example, there are multiple records of 1 location_gid in the table LOCATION_ROLE_PROFILE. 

The results that should come back from the example are: location_gid: 2 and 5

Solomon Yakobson

Use MINUS query:

 SELECT  LOCATION_GID
   FROM  LOCATION
MINUS
 SELECT  LOCATION_GID
   FROM  LOCATION_ROLE_PROFILE
   WHERE LOCATION_ROLE_GID = 'CUSTOMER'
/

SY.

Solomon Yakobson

Use MINUS query:

 SELECT  LOCATION_GID
   FROM  LOCATION
MINUS
 SELECT  LOCATION_GID
   FROM  LOCATION_ROLE_PROFILE
   WHERE LOCATION_ROLE_GID = 'CUSTOMER'
/

SY.

Bjornlaat

Thanks @Solomon Yakobson