SQL Query - Banging My Head
607373Dec 3 2007 — edited Dec 13 2007I'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.