-
I have two views with an ID and an id_code field. (The underlying table is the same.) How can I select all the ID's from view 1 that are not present in view two?
The following code returns the ID's if it exists, but the opposite returns no data. I know that there is data that is in view 1 but not view 2.
select
AZ.AN_ID,
AZ.ZIP_CODE,
from
AUTHOR_ZIPCODE AZ
WHERE exists
(select AN_ID from
AUTHOR_ZIPCODE_DUPES AZD
)
(Please forgive double posting if it happens...I can't see the 1st post)
ora_newbie@yahoo.com
-
Hi ,
select
AZ.AN_ID,
AZ.ZIP_CODE,
from
AUTHOR_ZIPCODE AZ
WHERE not exists
(select AZD.AN_ID from
AUTHOR_ZIPCODE_DUPES AZD
where AZ.AN_ID=AZD.AN_ID)
Hope it works....
-
Is there another way? The performance is unacceptable for a small test table < 500 records. (I let it run for 20 minutes before killing it).
I can't imagine this on a real table with > 1 million records.
ora_newbie@yahoo.com
-
How about this one:
select AN_ID
from
AUTHOR_ZIPCODE
minus
select AN_ID
from
AUTHOR_ZIPCODE_DUPES
Regards
Ben de Boer
-
-
from sql*plus:
WITH summary AS
(SELECT an_id FROM author_zipcode_dupes)
SELECT an_id FROM author_zipcode MINUS SELECT an_id FROM summary;
WITH clause increases performance.
F.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|