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('