Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2004
    Location
    Charlotte, NC
    Posts
    79

    Unanswered: Join tables and exclude records...

    Hi

    I am trying to write a SQL query to run against 2 Oracle tables - tblNames and tblAbsence.

    Let's say the tables look like this:

    tblNames:

    Andrew
    David
    John
    Michael

    and tblAbsence:

    Andrew 01/01/05 Sick
    Andrew 01/02/05 Sick
    David 01/07/05 Doctor's appointment

    What I need to do is to create a report that lists all of the absences from tblAbsence, plus a row for anyone in the tblNames table with no date and "In Office" in the third column if they don't have an entry in tblAbsence.

    To use the above values, what I should have in my resultset is:

    Andrew 01/01/05 Sick
    Andrew 01/02/05 Sick
    David 01/07/05 Doctor's appointment
    John Null In Office
    Michael Null In Office

    I'm not sure how to do this. I did try a UNION of two queries, but the nearest I have been able to get is to reproduce the entries in the tblAbsence table plus all of the records in tblNames. I need to exclude the names from tblNames if they have an entry in tblAbsence.

    Any ideas?

    Thanks

    Michael
    Make something idiot proof and someone will make a better idiot...

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Different versions of Oracle have different levels of support for standard SQL, but the way that I'd do it would be:
    Code:
    SELECT n.Name
    ,  a.date, Coalesce(a.comment, 'In Office)
       FROM tblNames AS n
       LEFT OUTER JOIN tblAbsence AS a
          ON (a.name = n.name)
    -PatP

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Another suggestion:
    Code:
    SELECT n.name, a.dtm, NVL(a.status, 'In office') status
    FROM tblNames n, tblAbsence a
    WHERE n.name = a.name (+);

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by Littlefoot
    Another suggestion:
    Code:
    SELECT n.name, a.dtm, NVL(a.status, 'In office') status
    FROM tblNames n, tblAbsence a
    WHERE n.name = a.name (+);
    Good point, but if the poster is just starting out I think it would be better to start them with standard SQL and only use eingine specific features if they are required. The closer we can keep the new users to standards, the less likely they are to get hurt by the odd quirks that we've come to know and love!

    -PatP

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I agree, Pat ... however, Robojan said it is about Oracle tables so I guess this Oracle specific code won't hurt much

  6. #6
    Join Date
    Feb 2004
    Location
    Charlotte, NC
    Posts
    79
    Thanks guys - this helped me to write the query. I simplified it greatly for this (removing joins, etc.), and using the joins correctly fixed it for me.

    Thanks
    Make something idiot proof and someone will make a better idiot...

Posting Permissions

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