1

I've got three tables:

AUTHOR(auth_id, fname, lname)
BOOKAUTHOR(auth_id, book_id)
BOOK(book_id, book_name, publish_date)

AUTHOR
auth_id  fname   lname
----------------------------
1        Bob     Bobson
2        Sam     Samson
3        Bill    Billson
4        Sally   Sallson
5        Mary    Marson

BOOKAUTHOR
auth_id    book_id
------------------
1          11
1          12
2          13
2          14
3          15
3          16
4          17
4          18
5          19
5          20

BOOK
book_id   book_name     publish_date
-------------------------------------
11        Bob Book 1    2015-06-05
12        Bob Book 2    2020-07-06
13        Sam Book 1    2016-04-03
14        Sam Book 2    2020-09-27
15        Bill Book 1   2013-08-20
16        Bill Book 2   2015-01-16
17        Sall Book 1   2012-06-27
18        Sall Book 2   2018-03-10
19        Mary Book 1   2003-08-01
20        Mary Book 2   2020-06-05

where BOOKAUTHOR is a bridge table.
I want to return three columns: author_name, name_of_their_first_book and date_it_was_published.
So far I have:

SELECT fname || ' ' || lname AS author_name, MIN(publish_date) AS publish_date
FROM author a, book b, bookauthor ba
WHERE a.auth_id = ba.auth_id
AND b.book_id = ba.book_id
GROUP BY author_name;

which returns:

author_name    publish_date
------------------------------
Bob Bobson     2015-06-05
Sam Samson     2016-04-03
Bill Billson   2013-08-20
Sally Sallson  2012-06-27
Mary Marson    2003-08-01

But when I try to add the book title though, like below

SELECT a.fname || ' ' || a.lname AS author_name, MIN(b.publish_date) AS publish_date, b.book_name AS latest_book
FROM author a, book b, bookauthor ba
WHERE a.auth_id = ba.auth_id
AND b.book_id = ba.book_id
GROUP BY author_name;

it returns the list of all books by the author, ignoring the MIN(b.publish_date):

author_name    publish_date   latest_book
-------------------------------------------
Bob Bobson     2015-06-05     Bob Book 1
Bob Bobson     2020-07-06     Bob Book 2
Sam Samson     2016-04-03     Sam Book 1
Sam Samson     2020-09-27     Sam Book 2
Bill Billson   2013-08-20     Bill Book 1
Bill Billson   2015-01-16     Bill Book 2
Sally Sallson  2012-06-27     Sall Book 1
Sally Sallson  2018-03-10     Sall Book 2
Mary Marson    2003-08-01     Mary Book 1
Mary Marson    2020-06-05     Mary Book 2

I imagine the correct solution is to use joins somehow, but I haven't wrapped my head around them too well. If it is a join, could you also please perhaps explain what they're doing?

Dr.Wilhouse
  • 15
  • 3
  • Please in code questions give a [mre]--cut & paste & runnable code, including smallest representative example input as code; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. Give the least code you can that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) For SQL that includes DBMS & DDL (including constraints & indexes) & input as code in table format. [ask] Pause work on the overall goal, chop code to the 1st expression not giving what you expect & say what you expect & why. – philipxy Oct 16 '20 at 08:04
  • 1
    Sorry, I thought what I'd added was sufficient. Thank you for letting me know. I'll edit it now. – Dr.Wilhouse Oct 16 '20 at 08:07
  • [Select first row in each GROUP BY group?](https://stackoverflow.com/q/3800551/3404097) This is a faq. Please before considering posting read your textbook and/or manual & google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. Reflect your research. See [ask] & the voting arrow mouseover texts. If you post a question, use one phrasing as title. – philipxy Oct 16 '20 at 08:09
  • I've been crawling all over the documentation and this website for hours and I haven't been able to find what I need, most likely through lack of understanding. I'm here asking because I couldn't find the answer I needed – Dr.Wilhouse Oct 16 '20 at 08:43
  • SO/SE search is poor. Google. Generally it's important to phrase clearly & generically... well, all the things in my comment. But just now googling 'author's earliest book site:stackoverflow.com' I get 1st hit https://stackoverflow.com/q/56244360/3404097 from 2019. If I preface with 'sql before:2010' then nothing. But '2015' I get https://stackoverflow.com/q/17392073/3404097. But normally one should phrase not mentioning authors & books, and try different words for extrema like greatest & first. There will be answers in 2009. Indeed this is so common there's a tag: [tag:greatest-n-per-group]. – philipxy Oct 16 '20 at 08:58
  • I appreciate you're trying to help, but I'd actually already read both of those, and neither of them deals with a bridge table. As I mentioned in my question, I'm still wrapping my head around joins, so I wasn't able to translate their solutions to my requirements. I even found this exact question elsewhere, but using a different SQL language, which I still couldn't figure out how to translate – Dr.Wilhouse Oct 16 '20 at 09:25
  • [Is there any rule of thumb to construct SQL query from a human-readable description?](https://stackoverflow.com/a/33952141/3404097) Good luck. – philipxy Oct 16 '20 at 10:09

1 Answers1

0

You can use joins and distinct on:

select distinct on (a.auth_id) a.*, b.*
from author a
inner join bookauthor ba on ba.auth_id = a.auth_id
inner join book b on b.book_id = ba.book_id
order by a.auth_id, b.publish_date
GMB
  • 216,147
  • 25
  • 84
  • 135
  • Not exactly what I was looking for (probably my fault for incomplete information), but it sent me in the right direction. Thanks heaps for your help. – Dr.Wilhouse Oct 17 '20 at 04:49