Multi tool use

Select subset for each item

I'm using SQL (from a PHP script) to get a list of items from my database, i.e.

SELECT `id`, `title` FROM `books` WHERE `removed` = NULL;


I also have a table containing articles, with a column bookId which refers to the id of the book.

bookId

SELECT `id`, `title` FROM `articles` WHERE `bookId`=1;


Now, I want to prepare a list of all books, and for each book I want to see all related articles (e.g. just the title).

So far, the only way I could image to get such a list is to first perform a query to get all the books, and then for each book to select the related articles. However, this would require a lot of separate SQL calls.

Does anyone have any idea wether it would be possible to get such a list with a single call?

2 Answers
2

You can change your second SQL query to:

SELECT `id`, `title`, 'bookId' FROM `articles`;


and then create new array with simple foreach, where $articles is array of results of this query:

$articlesByBook = ;
foreach ($articles as $article) {
$articlesByBook[$article['bookId']] = $article;
}


and then you can access all articles for given book by:

$articlesByBook[$bookId]


You can use a JOIN,

SELECT a.id,a.title ,b.id,b.title
FROM `books` a
LEFT JOIN `articles` b ON b.bookId=a.id
WHERE a.removed IS NULL;


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.