Join tables and exclude records...

robojam · #1 (**permalink**) 01-12-05, 13:55

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

Pat Phelan · #2 (**permalink**) 01-12-05, 14:22

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

Littlefoot · #3 (**permalink**) 01-12-05, 14:31

Another suggestion:

Code:

SELECT n.name, a.dtm, NVL(a.status, 'In office') status
FROM tblNames n, tblAbsence a
WHERE n.name = a.name (+);

Pat Phelan · #4 (**permalink**) 01-12-05, 14:53

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

Littlefoot · #5 (**permalink**) 01-13-05, 01:24

I agree, Pat ... however, Robojan said it is about Oracle tables so I guess this Oracle specific code won't hurt much

robojam · #6 (**permalink**) 01-13-05, 08:51

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

Internet Services

Web Development

Digital Marketing

Consumer Tech