the records with common cust_id should not be shown. [message #665257] |
Mon, 28 August 2017 05:41 |
|
adil shakeel
Messages: 47 Registered: August 2017
|
Member |
|
|
Hi all,
I have two tables. (One name with customers and other with name of Regions)
CUSTOMERS
Cust_ID Cust_Name
1 ALI
2 ASAD
3 ASAD
4 HUMA
5 NAZIA
6 SHUMAILA
7 PARVEEN
8 KIRAN
Regions
Cust_ID Area
1 Faisabad
6 karachi
9 lahore
11 peshawar
12 islamabad
I want to show the relevant records for only those cust_id which are not common in both these tables.
I run the following query but no rows return.
select customer.cust_id,cust_name, area from customers full outer join regions on customers.cust_id = regions.cust_id where customer.cust_id<>regions.cust_id;
Please guide me
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Re: the records with common cust_id should not be shown. [message #665400 is a reply to message #665384] |
Mon, 04 September 2017 02:26 |
|
quirks
Messages: 82 Registered: October 2014
|
Member |
|
|
All my solutions with INTERSECT contain a lot of MINUS and UNION or even 'NOT IN' (which is bad for performance) but probably I'm blind.
JPBoileau wrote on Fri, 01 September 2017 16:15Simpler yet, do a full outer join.
That's a neat idea.
WITH
CUSTOMERS
AS
(SELECT 1 AS CUST_ID, 'ALI' AS CUST_NAME FROM DUAL
UNION ALL
SELECT 2, 'ASAD' FROM DUAL
UNION ALL
SELECT 3, 'ASAD' FROM DUAL
UNION ALL
SELECT 4, 'HUMA' FROM DUAL
UNION ALL
SELECT 5, 'NAZIA' FROM DUAL
UNION ALL
SELECT 6, 'SHUMAILA' FROM DUAL
UNION ALL
SELECT 7, 'PARVEEN' FROM DUAL
UNION ALL
SELECT 8, 'KIRAN' FROM DUAL),
REGIONS
AS
(SELECT 1 CUST_ID, 'Faisabad' AREA FROM DUAL
UNION ALL
SELECT 6, 'karachi' FROM DUAL
UNION ALL
SELECT 9, 'lahore' FROM DUAL
UNION ALL
SELECT 11, 'peshawar' FROM DUAL
UNION ALL
SELECT 12, 'islamabad' FROM DUAL)
SELECT
COALESCE(CUSTOMERS.CUST_ID, REGIONS.CUST_ID) AS CUST_ID,
CUSTOMERS.CUST_NAME,
REGIONS.AREA
FROM CUSTOMERS
FULL OUTER JOIN REGIONS
ON (CUSTOMERS.CUST_ID = REGIONS.CUST_ID)
WHERE
CUSTOMERS.CUST_ID IS NULL
OR REGIONS.CUST_ID IS NULL
ORDER BY
COALESCE(CUSTOMERS.CUST_ID, REGIONS.CUST_ID)
[Updated on: Mon, 04 September 2017 02:29] Report message to a moderator
|
|
|