sql - Oracle: union all query 1 and query 2 want to minus some rows if query 1 have rowdata

Question

my query as below , i want to minus some rows from query1 when query2 have rowdata , but i don't know how to do:

my query:

with query1 as(
    select wm_concat(linkman_name) name,
           wm_concat(phone_num) phone,
           t.org_id
    from (
             select linkman_name, phone_num, LINK_ORG_ID, org_id
             from TD_SM_LINKMAN
             where STATE = '2'
               and (LINK_ORG_ID is null or LINK_ORG_ID = '')) t
    group by t.org_id) ,
     query2 as(
         select wm_concat(linkman_name) name,
                wm_concat(phone_num) phone,
                org_id
         from (select linkman_name, phone_num, LINK_ORG_ID, org_id
               from TD_SM_LINKMAN
               where STATE = '2'
                 and (LINK_ORG_ID = '55')) t
         group by org_id)
select *
from query1
union all
select *
from query2 minus 
-- this doesn't work ,i want to minus the rowdata from query 1 when query1.org_id = query2.org_id. the query2 is marked as outer query column.
(select  * from query1 where query1.ORG_ID = query2.ORG_ID)
;

sample table

name         phone           link_org_id      org_id
lily         133                                1
ming         144                                1
hao          333                                2
jane         1234               55              2
bob          666                                3
herry        555                                3

query 1 result:

name          phone           org_id
lily,ming    133,144          1
hao          333              2
bob,herry    666,555          3

query 2 result:

name          phone           org_id
jane          1234             2

such like this , jane selected by query2 and hao selected by query 1 . All of them are from a same org which org_id =2 . but i don't need hao ,i just need jane. how to do? i means if query2 can find result , then no need query1's result. but if query2 can't find any data, then i need query1's data.

Answer 1

The way it is now, you'll first have to split names (and phones) into rows, and then apply set operators (UNION, MINUS) to such a data.

Which means that you shouldn't use WM_CONCAT at all; at least, not at the beginning, because

• first you concatenate data
• then you'd have to split it back into rows
• UNION / MINUS sets

Doing useless job in the first 2 steps.

I'd suggest you to UNION / MINUS data first, then aggregate them using WM_CONCAT. By the way, which database version do you use? WM_CONCAT is a) undocumented, b) doesn't even exist in latest Oracle database versions so you'd rather switch to LISTAGG, if possible.