MySQL simple query about join

Question

I'm Univ. student from Korean, so sorry to my great english ability first :D

I design the simple bookstore database, but it's hard for me to write query :( How can I print like below with that ERD ?

[ISBN, title, writer.name, page, price, publisher.name, publish_date, topic, review]

If writers and topics are 2 and more, and then print tuples all. Would you help me guys?

Please paste the table details directly in question rather than as image. Also provide us sample input and expected output. — SMA, May 25 '17 at 12:44
See: [Why should I provide an MCVE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) — Strawberry, May 25 '17 at 12:49

score 1 · Answer 1 · answered May 25 '17 at 12:56

its a simple question, when you want to find any value from join tables think like a procedural query:

need customer.address , base table : book so: book join purchache -> join customer -> catch the value # it will die here, lets to another value.

need topic.name , base table : book so: book join topic -> catch the value # next

no more , lets do the query:

select 
    book.ISBN
    book.title
    book.price
    book.publisher_date
    writer.name as WriterName /* if you need an alias */
    publisher.name as PublisherName /* if you need an alias */
    topic.name as TopicName /* if you need an alias */
    purchase.review
    /* bonus */
    customer.phone_number

 from book 
     left join writer on writer.cook_id = book.id
     left join topic on topic.cook_id = book.id
     left join publisher on publisher.id = book.publisher_id
     left join purchase on purchase.book_id = book.id /* first */
         left join customer on purchase.customer_id = customer.id /* second */

i think it works. :)

Could I ask you 1 more question? I want to print like that [writer_name, publisher_name, count] count is how many books that writer publish in publisher and my query is... select writer.name as writer_name, publisher.name as publisher_name from writer natural join book, publisher where book.publisher_id = publisher.id order by writer_name — dhpark, May 25 '17 at 13:35
your count(publisher.id), but it right, if you need to list more than one publisher you need an group by publisher.id , and for the join, if the "from" is the writer, to reach publisher you need to join writer -> book -> publisher — israel, May 25 '17 at 13:47

score 0 · Accepted Answer · answered May 25 '17 at 12:56

Just a quick mock up. I have made assumptions on the table structure. Three tables, one for the book, one for the author one for reviews.

I have used a left join as i assumed you would be querying based on the book, the ISBN being that tables unique-key. Joins can take a little time to figure out, so if you need help with joins this site may help out. https://www.techonthenet.com/mysql/joins.php

select
        boo.ISBN, 
        boo.title, 
        au.author_name,
        boo.pages,
        boo.price,
        boo.publisher,
        boo.publish_date,
        boo.genre,
        rev.review  

        from books as boo
            left join author as au
                on boo.authorID = au.authorID
            left join reviews as rev
                on rev.ISBN = boo.ISBN
        where boo.ISBN = XXXX;

If you need help making the ERD itself, there is another stackexchange article that may help out if i misread the initial question this link here

MySQL simple query about join

2 Answers2