CODEWITHSUNDEEP
mysqlone-to-many
Edwin Wong
Edwin Wong

Reputation: 713

MySQL one to many relationship in one query

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?

Upvotes: 5

Views: 23615

Answers (4)

zakhefron
zakhefron

Reputation: 1443

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

Upvotes: 8

Gruber
Gruber

Reputation: 4558

If you want to do it quick and simple, use GROUP_CONCAT with an appropriate delimiter character. Then split the resulting concatenated string field in your PHP application (or whatever language you're using) to quickly get the inner array you want.

This has the advantage that you will only have one row per book in the output.

Upvotes: 4

Piyush Aghera
Piyush Aghera

Reputation: 965

A Way that you want data might not be easy from mysql. Mysql will return as row wise. so Either books or author will repeat in result set. and you have to parse mysql result accordingly to prepare you array.

Try following:

1) All books with author as comma separated ids.

"SELECT B.*,group_concat(a.id) as authors 
 From book b inner join authors as a on b.author_id=a.id 
 group by b.id;"

2) All authors with book details:

"SELECT A.*,group_concat(B.id) as books 
 From book B inner join authors as A on B.author_id=A.id 
 group by A.id;"

Upvotes: 1

arnoudhgz
arnoudhgz

Reputation: 1284

SELECT b.*, a.*
FROM book b
INNER JOIN authors a ON (a.book_id = b.id);

Upvotes: 0

Related Questions