CODEWITHSUNDEEP
mysql
fittaoee
fittaoee

Reputation: 145

Single MySQL statement to query two tables

I currently have two tables related to a book. The first table, BOOK, looks like below:

BOOK_ID,  TITLE,  AUTHOR, PUBLISH_TIME, ...

And the second table, BOOK_CHAPTER, is about the chapters that the books have. Each row in this table corresponds to a chapter in a book, and a book has multiple chapters:

BOOK_ID, CHAPTER_ID, CHAPTER_STATUS, ...

My question is, given a BOOK_ID, is it possible to use one single statement to return the TITLE, AUTHOR of a book, as well as all the CHAPTER_ID and CHAPTER_STATUS that's related to this particular book? I've come up with two separate select statements that accomplish the goal, but since the returned result from BOOK table is a single row, and that from BOOK_CHAPTER contains multiple rows, I'm wondering whether it's possible to combine the two into one statement. Thanks in advance.

Upvotes: 0

Views: 34

Answers (3)

R.K123
R.K123

Reputation: 159

Create an outer query that selects the book_id from the result of joining your two queries;Include book_id in the select lists of each subquery; and Join on both book_id and the counts

   SELECT COUNT(*) 
    FROM (books_authors 
    WHERE books_authors.author_id IN 
            (
               SELECT author_id 
               FROM obl_authors WHERE bookchapter='whatevernameis' 
            )  GROUP BY books_authors.book_id) A

INNER JOIN 
(
    SELECT COUNT(*) 
    FROM books_authors 
    GROUP BY books_authors.book_id 
) B
ON A=B

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521073

MySQL's GROUP_CONCAT might work well here. It can aggregate all chapter IDs and statuses for a given book as a comma-separated list. This CSV list could then be displayed in a single record for each book.

SELECT b.TITLE,
       b.AUTHOR,
       GROUP_CONCAT(bc.CHAPTER_ID SEPARATOR ','),
       GROUP_CONCAT(bc.CHAPTER_STATUS SEPARATOR ','),
FROM BOOK b
INNER JOIN BOOK_CHAPTER bc
    ON b.BOOK_ID = bc.BOOK_ID
GROUP BY b.BOOK_ID

Upvotes: 0

JNevill
JNevill

Reputation: 50034

You use a JOIN in your FROM clause to join two tables together and return results from both:

SELECT BOOK.*, BOOK_CHAPTER.*
FROM BOOK INNER JOIN BOOK_CHAPTER ON
    BOOK.BOOK_ID = BOOK_CHAPTER.BOOK_ID
WHERE BOOK.BOOK_ID=<whateverbookyouarequerying>

Learn more about Join's in MySQL
Learn more about Join's in general

Upvotes: 1

Related Questions