CODEWITHSUNDEEP
sqloracle-databaseoracle-sqldeveloper
Sanguinary
Sanguinary

Reputation: 354

Find row that does not exist

Introduction

I got a table with merchants that has one admin merchant in one row and one normal merchant in another row. The only difference between the two are the names. The Admin merchant has "Admin" at the end of the name. They both use the same merchant group oid.

My problem

There are some rows that does not have a related normal merchant and I would like to list all rows of admin merchants that does not have a row of a normal merchant. Using the example below I'd like to only list up Merchant 2 Admin

Example:

Rows (column: name):
Merchant 1 Admin
Merchant 1
Merchant 2 Admin

I made a query which finds all admin merchants that has a normal merchant, but how can I modify this to only find the ones with an admin merchant and no normal merchant?

select t1.oid,t1.name, t2.oid, t2.name From 
(select * from merchants where upper(name) like upper('%Admin%')) T1 ,
(select * from merchants where upper(name) not like upper('%Admin%')) T2
where t1.merchant_group_oid = t2.merchant_group_oid;

Upvotes: 0

Views: 43

Answers (1)

Littlefoot
Littlefoot

Reputation: 142705

Would this do? It displays who's missing.

Sample data:

SQL> with merchants (name) as
  2    (select 'Merchant 1 Admin' from dual union all
  3     select 'Merchant 1'       from dual union all
  4     select 'Merchant 2 Admin' from dual
  5    )

Query begins here, utilizing the MINUS set operator:

  6  select trim(replace(name, 'Admin')) missing from merchants
  7  minus
  8  select name from merchants;

MISSING
----------------
Merchant 2

SQL>

Or:

<snip>
  6  select trim(replace(name, 'Admin')) || ' Admin' misses_normal_merchant
  7  from merchants
  8  group by trim(replace(name, 'Admin'))
  9  having count(*) = 1;

MISSES_NORMAL_MERCHANT
----------------------
Merchant 2 Admin

SQL>

Upvotes: 2

Related Questions