Skip to Main Content

APEX

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

SQL Query - Banging My Head

607373Dec 3 2007 — edited Dec 13 2007
I've been struggling with this for a couple of days and could use some help -

I have 3 tables

Books (with an ID and Title)
Authors (with a Book_Link and a Name)
Publications (with a Book_Link and a Publication_Date)

Each book can have 0 or more authors. Each book can have 0 or more publications. I ultimately need a report that shows

Book Title/Author Name/Publication Date

So, let's say there are 3 books (ID/Title)
#1 - Book One
#2 - Book Two
#3 - Book Three

Book One has 2 authors and 3 publication dates.
Book Two has 1 author and 1 publication date
Book Three has no authors or publication dates.

With regular joins, I get:

Book One - Author 1A - Pub Date 1-1
Book One - Author 1A - Pub Date 1-2
Book One - Author 1A - Pub Date 1-3
Book One - Author 1B - Pub Date 1-1
Book One - Author 1B - Pub Date 1-2
Book One - Author 1B - Pub Date 1-3
Book One - Author 1C - Pub Date 1-1
Book One - Author 1C - Pub Date 1-2
Book One - Author 1C - Pub Date 1-3
Book Two - Author 2A - Pub Date 2-1
Book Three - null - null

What I need to report to my user is:

Book One - Author 1A - Pub Date 1-1
Book One - Author 1B - Pub Date 1-2
Book One - null - Pub Date 3
Book Two - Author 2A - Pub Date 2-1
Book Three - null - null

I am using Apex so any functions or whatnot would be helpful.

One other condition - this is a query that I am building dynamically, based on the user selecting which fields they want reported on. So any solution needs to account for the fact that the select statement and whatnot need to be constructed as string.

Thanks.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jan 10 2008
Added on Dec 3 2007
16 comments
1,500 views