Solved

Sql to get orphans

Posted on 2016-08-31
7
68 Views
Last Modified: 2016-09-01
Hi Experts,

The database is Oracle 11g
I have table Parent  with column  ( ID ,name)
 The table Child has column ChildID , ParentID  ,Name
 
sample data
Parent
    P1   Parent1
     P2  Parent2

Child
   C1 P1  Child1
   C2  P1  Child2
   C3  P3  Child3

it is expected for each ChildID there has to be a corresponding
There are some  orphaned rows in the table Child  . In the sample data Child3 is an orphan because there is no P3 in Parent table

I need help in getting the Sql that can give the orphan children
0
Comment
Question by:Sam OZ
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 49

Accepted Solution

by:
PortletPaul earned 250 total points
ID: 41777661
select
       c.*
from child c
left join parent p on c.ParentID = p.id
where p.id IS NULL

Open in new window

0
 

Author Comment

by:Sam OZ
ID: 41777690
Thanks .  Infact in my table it is p.UID instead of p.ID    For some reason , p.UID throws error ( Because UID is a reserved word it looks ( Even select p.UID from parent p throws error)
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 41777772
What is the exact error message?
Is p.uid an integer?
0
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
LVL 35

Expert Comment

by:johnsone
ID: 41778108
You would need to put it in double quotes:

select p."UID" from parent p

The double quotes makes is case sensitive, so make sure it is upper case.

This is a total design flaw, you should never have column names with reserved words.

An alternate way to find duplicates:

Create an exception table by running this in SQL*Plus:
@?/rdbms/admin/utlexcpt

Then create a referential integrity constraint (something you should have to prevent this in the first place):
alter table child add foreign key (id) references parent("UID") exceptions into exceptions;

Then you can get all the invalid rows with this:
select * from child where rowid in (select row_id from exceptions);

Let Oracle do the work for you.
0
 
LVL 32

Expert Comment

by:awking00
ID: 41778136
Do you have foreign key constraints on the child tables?
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 41778861
UID returns an integer that uniquely identifies the session user (the user who logged on).
https://docs.oracle.com/cd/B28359_01/server.111/b28286/functions211.htm

certainly not a good idea to use that as a column name
0
 
LVL 8

Assisted Solution

by:Ghunaima
Ghunaima earned 250 total points
ID: 41780292
select * from child where parentid not in (select p."UID" from Parent p)
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to take different types of Oracle backups using RMAN.

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question