Database mysql trying to find out how it all connecting together, all authors that have published mi-CodePudding

So i got this database.

CONSULTANT(cons_id, firstname, lastname, epost)
BOOK(book_id, title, published_year, publisher_id*)
PUBLISHER(publisher_id, publisher_name, adresse, phone)
AUTHOR(author_id, firstname, lastname, birth_year, year_of_death, nationality)
BOOK_AUTHOR(book_id*, author_id*)

I am just trying to understand how it all connect together.

I need help getting all authors that have published minimum one book with the publisher Cappelen. I am not sure how i can show the database on this site?

UPDATED! Was a little to fast to ask a question, forgot the code.

This is the code i have now

SELECT au.firstname, au.lastname, publisher_name, title
FROM author au, publisher pu, book b;

this gives me

So i just need a way to choose all the authors that has published on Cappelen.

CodePudding user response：

Solving your problem requires combining several SQL techniques.

JOIN: To get a resultset of authors, their books, and those books' publishers you do this.

SELECT AUTHOR.firstname, AUTHOR.lastname,
       BOOK.title, BOOK.published_year,
       PUBLISHER.publisher_name
  FROM AUTHOR
  JOIN BOOK_AUTHOR ON AUTHOR.author_id = BOOK_AUTHOR.author_id
  JOIN BOOK        ON BOOK_AUTHOR.book_id = BOOK.book_id
  JOIN PUBLISHER   ON BOOK.publisher_id = PUBLISHER.publisher_id

The JOIN operations declare the relationships between rows in your various tables. Give this a try. Get familiar with how JOINs work -- they put the relational in relational database management system.

(Notice there are no id numbers I can see that relate your CONSULTANT table's rows to any other tables' rows. Perhaps there is another table you haven't described? But the answer to your question does not need CONSULTANT.)

COUNT: Once you know how to get that author / book / publisher result set, then you modify it do an aggregate (COUNT / GROUP BY) query.

SELECT COUNT (DISTINCT book_id) book_count,
       AUTHOR.firstname, AUTHOR.lastname,
       PUBLISHER.publisher_name
  FROM AUTHOR
  JOIN BOOK_AUTHOR ON AUTHOR.author_id = BOOK_AUTHOR.author_id
  JOIN BOOK        ON BOOK_AUTHOR.book_id = BOOK.book_id
  JOIN PUBLISHER   ON BOOK.publisher_id = PUBLISHER.publisher_id
 GROUP BY AUTHOR.firstname, AUTHOR.lastname, PUBLISHER.publisher_name

COUNT(DISTINCT book_id) gets the number of distinct (different) books for the GROUP BY AUTHOR and PUBLISHER information.

WHERE filtering: Finally you throw in a WHERE clause to choose just one publisher.

SELECT COUNT (DISTINCT book_id) book_count,
       AUTHOR.firstname, AUTHOR.lastname,
       PUBLISHER.publisher_name
  FROM AUTHOR
  JOIN BOOK_AUTHOR ON AUTHOR.author_id = BOOK_AUTHOR.author_id
  JOIN BOOK        ON BOOK_AUTHOR.book_id = BOOK.book_id
  JOIN PUBLISHER   ON BOOK.publisher_id = PUBLISHER.publisher_id
 WHERE PUBLISHER.publisher_name = 'Cappelen'
 GROUP BY AUTHOR.firstname, AUTHOR.lastname, PUBLISHER.publisher_name

If you wanted to see authors with two or more books, you append HAVING COUNT(DISTINCT book_id) >= 2 to the end of that query.