Home » SQL & PL/SQL » SQL & PL/SQL » the records with common cust_id should not be shown.
the records with common cust_id should not be shown. [message #665257] Mon, 28 August 2017 05:41 Go to next message
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 #665259 is a reply to message #665257] Mon, 28 August 2017 05:48 Go to previous messageGo to next message
Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Set operators might be used here. CUST_IDs common in both tables can be found using INTERSECT. CUST_IDs from the CUSTOMERS table that aren't contained in the above set: NOT IN. So:
select ...
from customers
where cust_id NOT IN (select ...
                      INTERSECT
                      select ...
                     );
Re: the records with common cust_id should not be shown. [message #665262 is a reply to message #665259] Mon, 28 August 2017 06:27 Go to previous messageGo to next message
adil shakeel
Messages: 47
Registered: August 2017
Member
please write the full code rather than ...............
I did not get that
Re: the records with common cust_id should not be shown. [message #665263 is a reply to message #665262] Mon, 28 August 2017 07:00 Go to previous messageGo to next message
adil shakeel
Messages: 47
Registered: August 2017
Member
I want the following result.


Cust_id cust_name area

2 ASAD null
3 ASAD null
4 HUMA null
5 NAZIA null
7 PARVEEN null
8 KIRAN null
9 null lahore
11 null peshawar
12 null islamabad


Please guide me
Re: the records with common cust_id should not be shown. [message #665264 is a reply to message #665263] Mon, 28 August 2017 07:02 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Why not CUST_ID=1 in result set?


Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read
Re: the records with common cust_id should not be shown. [message #665268 is a reply to message #665264] Mon, 28 August 2017 07:21 Go to previous messageGo to next message
adil shakeel
Messages: 47
Registered: August 2017
Member
because the cust_id is present in the other table.
So want to get the information for the unique cust_id only
Re: the records with common cust_id should not be shown. [message #665269 is a reply to message #665259] Mon, 28 August 2017 07:23 Go to previous messageGo to next message
adil shakeel
Messages: 47
Registered: August 2017
Member
with selection from customers table only would not fetch records from the table REGIONS

And I want to fetch the records from the regions table too.
Re: the records with common cust_id should not be shown. [message #665277 is a reply to message #665262] Mon, 28 August 2017 09:43 Go to previous messageGo to next message
Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
adil shakeel wrote on Mon, 28 August 2017 13:27

please write the full code rather than ...............
I did not get that

Sorry, I won't do that. This is really a simple code which you should be able to complete in a matter of seconds (only if you switch your brain ON).
Re: the records with common cust_id should not be shown. [message #665289 is a reply to message #665277] Tue, 29 August 2017 00:37 Go to previous messageGo to next message
adil shakeel
Messages: 47
Registered: August 2017
Member
select customer.cust_id,cust_name, area from customers full outer join regions on customers.cust_id = regions.cust_id where customer.cust_id not in (select cust_id from customers union select cust_id from regions);


Is it the correct code?

Will it get the records from both tables against those cust_id which is un-common in both tables?
Re: the records with common cust_id should not be shown. [message #665293 is a reply to message #665289] Tue, 29 August 2017 03:42 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
No - union gives the list of cust_ids in that are in either table.
You want the sub-query to only give the cust_ids that are present in both tables. Which, as LF already told you, is what intersect does.
So use intersect.
Re: the records with common cust_id should not be shown. [message #665299 is a reply to message #665293] Tue, 29 August 2017 06:30 Go to previous messageGo to next message
adil shakeel
Messages: 47
Registered: August 2017
Member
ThankS

it worked.

Thanks @ cookiemonster and LITTLEFOOT BOTH OF YOU
Re: the records with common cust_id should not be shown. [message #665384 is a reply to message #665299] Fri, 01 September 2017 16:15 Go to previous messageGo to next message
JPBoileau
Messages: 88
Registered: September 2017
Member
Simpler yet, do a full outer join.

select nvl(a.cust_id, b.cust_id)
from customers a full outer join customers2 b on a.cust_id =b.cust_id;

I don't have a way to test it right quick but it should be quite close.

JP
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 Go to previous message
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:15
Simpler 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

Previous Topic: SQL Query
Next Topic: Select u'' from dual; What does u mean? in this sql.
Goto Forum:
  


Current Time: Thu Apr 25 01:57:30 CDT 2024