CODEWITHSUNDEEP
sqloracle-databaseouter-join
DNkG
DNkG

Reputation: 49

Using traditional join clause with the (+) operator

I'm really stuck with a SQL question from the JustLee Database. NOT looking for anyone to do my homework but unfortunately I am stuck...

Questions: Show all authors and books they have written. Include authors that haven't finished writing their books yet. Include author last name, author first name, isbn, and book title. Use traditional join clause with the (+) operator.

My best guess is:

select lname, fname, isbn, title
from author a, bookauthor ba, books b
where a.authorid(+) = ba.authorid
and ba.isbn(+) = b.isbn
order by 1,2,3,4;

But I get a column ambiguously defined error. Any help would be much appreciated. Thanks!

Upvotes: 3

Views: 2444

Answers (3)

D Stanley
D Stanley

Reputation: 152644

You need to qualify the ambiguous column name in the SELECT list:

select lname, fname, b.isbn, title
from author a, bookauthor ba, books b
where a.authorid(+) = ba.authorid
and ba.isbn(+) = b.isbn
order by 1,2,3,4;

It's generally good practice to qualify ALL columns in the select if nothing else than to make it clear what table the column is coming from. I also suggest you switch to modern JOIN syntax, and to list the columns explicitly in the ORDER clause:

select a.lname, a.fname, b.isbn, b.title
from author a
LEFT JOIN bookauthor ba
ON a.authorid = ba.authorid
LEFT JOIN books b
and ba.isbn = b.isbn
order by a.lname, a.fname, b.isbn, b.title;

Upvotes: 4

user5683823
user5683823

Reputation:

If the data model is correct, then authorid should be primary key in author and foreign key in bookauthor. Similarly, isbn should be primary key in books and foreign key in bookauthor. You are querying primarily for authors, and - presumably - you may have authors who have not written any books, so it is possible that they would be in the author table but not in the bookauthor table (although I would question why they are in the author table to begin with). The problem statement says "they haven't finished writing their books", so let's go with that.

To get the authors who appear only in the author table but not in bookauthor, you need a left outer join between author and bookauthor. Using Oracle proprietary notation (the (+) notation), the (+) should be on the right side of the equality:

... from author a, bookauthor ba, books b
where a.authorid = ba.authorid(+)
  and ba.isbn    = b.isbn

Notice two things:

For a left join, the (+) is on the right. To remember it: the "outer" join creates "fictitious" rows for the authorid values that are present in the table on the left but are otherwise missing on the right. So you need to add these "made-up" or "imaginary" rows to the table on the right (not on the left). So the (+) is on the right.

For isbn an inner join should suffice - there should be no book in the bookauthor table that doesn't correspond to a book in the books table. (Certainly not if the tables have the proper foreign and primary key as discussed above.)

Note: I would be quite suspicious of a book that wants to force you to learn the old, deprecated (+) syntax (and more generally "join syntax through conditions in the WHERE clause" as opposed to the modern, preferred, ANSI standard syntax using the ON or USING clause).

Upvotes: 2

Robby Cornelissen
Robby Cornelissen

Reputation: 97381

One or more of the columns you're selecting exist in multiple tables. You have to qualify them.

Looks like the isbn column exists in both the books and bookauthor table, so instead of selecting isbn, you should select b.isbn or ba.isbn.

Upvotes: 0

Related Questions