I have two select queries, where query A has all the data and I have another query with some data. We need to fetch the uncommon data from both query records. Let's say (query A - query B).
Please find below example with existing data and expected output.
Query A:
SELECT meaning
FROM apps.fnd_lookup_values
WHERE lookup_type = 'XXLSC_SCHEDULE_PRO_LKP';
Output of Query A:
| Meaning |
|---|
| Autoinvoice Import Program |
| Purge Concurrent Request and/or Manager Data |
| Create Intercompany AP Invoices |
| Rollup Cumulative Lead Times |
| Record Order Management Transactions |
Query B:
SELECT DISTINCT fcs.program
FROM apps.fnd_concurrent_requests fcr,
apps.fnd_concurrent_programs_tl fcp,
apps.fnd_responsibility_tl frl,
apps.fnd_user fu,
apps.fnd_conc_req_summary_v fcs
WHERE fcr.phase_code = 'P'
AND fcr.request_id = fcs.request_id
AND frl.language = 'US'
AND fcr.requested_by = fu.user_id
AND fcr.responsibility_id = frl.responsibility_id
AND fcr.status_code IN ('P','Q')
AND fcp.language = 'US'
AND fcp.source_lang = 'US'
AND fcr.concurrent_program_id = fcp.concurrent_program_id
AND fcr.requested_start_date >= SYSDATE
AND fcs.program IN (SELECT meaning FROM apps.fnd_lookup_values
WHERE lookup_type = 'XXLSC_SCHEDULE_PRO_LKP');
Output of query B:
| Program |
|---|
| Autoinvoice Import Program |
| Create Intercompany AP Invoices |
| Record Order Management Transactions |
Below is the final expected output (fetch the uncommon data from both queries - query A which will have all the data will be minus from query B records):
| Meaning |
|---|
| Purge Concurrent Request and/or Manager Data |
| Rollup Cumulative Lead Times |
Can it be achieved from INNER JOIN/OUTER JOIN/EXPECT/UNION or anything else?
"query A - query B" is actually a great way of describing it, as Oracle has a minus set operator:
SELECT meaning -- This is query A
FROM apps.fnd_lookup_values
WHERE lookup_type = 'XXLSC_SCHEDULE_PRO_LKP';
MINUS -- Set operator here
SELECT fcs.program -- This is query B, with the DISTINCT modifier removed
FROM apps.fnd_concurrent_requests fcr,
apps.fnd_concurrent_programs_tl fcp,
apps.fnd_responsibility_tl frl,
apps.fnd_user fu,
apps.fnd_conc_req_summary_v fcs
WHERE fcr.phase_code = 'P'
AND fcr.request_id = fcs.request_id
AND frl.language = 'US'
AND fcr.requested_by = fu.user_id
AND fcr.responsibility_id = frl.responsibility_id
AND fcr.status_code IN ('P','Q')
AND fcp.language = 'US'
AND fcp.source_lang = 'US'
AND fcr.concurrent_program_id = fcp.concurrent_program_id
AND fcr.requested_start_date >= SYSDATE
AND fcs.program IN (SELECT meaning FROM apps.fnd_lookup_values
WHERE lookup_type = 'XXLSC_SCHEDULE_PRO_LKP');