MySQL one to many relationship in one query

Question

How can I get data one to many relationship in ONE query?

Example: one book has many authors.

The result return should be like this:

array(
   'book_title' => 'HTML for Dummies',
   'book_desc'  => '...',
   'authors' => array(
       [0] => array(
           'name' => 'Someone',
           'dob'  => '...'
       ),
       [1] => array(
           'name' => 'Someone',
           'dob'  => '...'
       )
    )
)

I have tried using subquery to select the result but not luck:

SELECT *, (
    SELECT * 
    FROM authors
    WHERE book_id = b.id
) AS authors
FROM book b;

Mysql error "Operand should contain 1 column(s)" which means that I only can select one column.

You may suggest me using join but how it can archive the return result format like I have shown you?

Answer 1

SELECT 
 B.id AS book_id, B.name AS book_name,
 A.name AS author_name ,A.ID AS author_id 
FROM book B
LEFT JOIN 
 author A ON B.id = A.book_id;

Refer : http://sqlfiddle.com/#!9/633cfa1/2

Your output will be in numeric array.

Array
(
    [0] => Array
        (
            [bookId] => 1
            [bookName] => Book 1
            [authorId] => 1
            [authorName] => Author 1
        )

    [1] => Array
        (
            [bookId] => 1
            [bookName] => Book 1
            [authorId] => 2
            [authorName] => Author 2
        )

    [2] => Array
        (
            [bookId] => 2
            [bookName] => Book 2
            [authorId] => 3
            [authorName] => Author 3
        )

    [3] => Array
        (
            [bookId] => 3
            [bookName] => Book 3
            [authorId] => 4
            [authorName] => Author 4
        )

)

Convert this array to multidimensional array

Array
(
    [0] => Array
        (
            [bookId] => 1
            [bookName] => Book 1
            [authorName] => Array
                (
                    [0] => Author 1
                    [1] => Author 2
                )

        )

    [1] => Array
        (
            [bookId] => 2
            [bookName] => Book 2
            [authorName] => Array
                (
                    [2] => Author 3
                )

        )

    [2] => Array
        (
            [bookId] => 3
            [bookName] => Book 3
            [authorName] => Array
                (
                    [3] => Author 4
                )

        )

)

Refer : https://3v4l.org/q1dli