Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.
Below is my query. The main query (on "books") returns 100+ records which all belong to "user" requested. The internal query (on "book_user") returns 3 records (which is what I want). However, the entire query below returns 4 records (which is all the records inside the "book_collection" table, including books that do not belong to the user). Any ideas as to how I can request only the 3 found in the sub-query?
SELECT *
FROM books
INNER JOIN book_user
ON books.id = book_user.book_id
INNER JOIN
(
SELECT book_user.book_id, book_user.created_at
FROM book_user
INNER JOIN book_collection
ON book_user.id = book_collection.book_user_id
WHERE book_collection.collection_id = 4
AND book_user.user_id = 1
) AS bu
ON books.id = bu.book_id
WHERE book_user.user_id = 1
I'm not a fan of the new way of writing joins I much prefer the old syntax. However something like this should work for you.
SELECT b.*
FROM books b,
book_user bu,
book_collection bc
WHERE b.id = bu.book_id
AND bu.user_id = bc.user_id
AND bc.collection_id = 4
AND bu.user_id = 1
@Tray2 ... not working. I had to modify as well since "book_collection" doesn't contain the "user_id" but "collections" does. Either way, It repeated a lot of records and the resulted in all the books, not just those in "book_collection." I think I need to work with my original query.
SELECT b.*
FROM books b,
book_user bu,
book_collection bc,
collections c
WHERE b.id = bu.book_id
AND bu.user_id = c.user_id
AND bc.collection_id = 4
AND bu.user_id = 1
GROUP BY b.id
That is because you don't join bc with any of the other tables.
Please or to participate in this conversation.