Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2004
    Location
    Aust
    Posts
    3

    Question Unanswered: Parent table, which doesnt have child data

    Hi,

    is there any sql command in oracle that can show all parent data that doesnt have child record??

    for example:
    there are two tables STUDENT and STUDENT_SUBJECT.
    I want to display all the data in the STUDENT table which doesnt have the child data in STUDENT_SUBJECT.

    any solution for this??

    thanks for the reply...

    btw i'm using oracle 9i
    Last edited by m1dway; 01-11-04 at 07:09.

  2. #2
    Join Date
    Sep 2003
    Location
    Milan, Italy
    Posts
    130
    select * from student s where not exists (
    select null from student_subject ss
    where ss.student_id = s.student_id);

    select * from student s where student_id not in (
    select student_id from student_subject
    WHERE student_id IS NOT NULL
    )

    Or, using an outer join:

    select s.*
    from student s, student_subject ss
    where s.student_id = ss.student_id(+)
    and ss.rowid is null;

    Probably in this case the 2nd or 3rd variation are the fastest - but you must check and test in your environment.

    Al

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •