mercredi 6 juillet 2016

MySQL one to many relationship in one query

how i can archive 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'  => '...'
       )
    )
)

what 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 mean i only can select one column.

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

Aucun commentaire:

Enregistrer un commentaire