CODEWITHSUNDEEP
phpmysqljoin
cyclone200
cyclone200

Reputation: 387

Left join with filter

I'm trying to display a list of books from a table (books) but I want to display the name of the book in green if the user connected added the book in his collection. The collections are in the table collection. So I need to retrieve all the informations from the books table but I need to differentiate the books owned by the user.

I tried something like this :

SELECT * FROM books LEFT JOIN collection ON books.ID = collection.ID_book AND collection.ID_member = :ID_member WHERE books.ID_author = :ID_author

:ID_member is the ID in session of the member logged in and ID_author is the name of the author I want to display the list. But with that query, I cannot retrieve the ID of books that the user doesn't own. Do you have an idea to retrieve these ID too ?

I thought of a second query inside the while() but that would means a query PER book...

EDIT (more informations) :

I have a table books with all the informations concerning the books. Then, I have a table collection with these columns : ID, ID_member, ID_book

With my query, if I want to display all books from... let's say Stephen King, I have something like this :

book || ID of the book || book owned by the user
1    || 1              || 0
2    || 2              || 0 
3    ||                || 1
4    || 4              || 0 
5    ||                || 1 
6    || 6              || 0

The ID I want to display is the collection.ID_book because if I display ID, it will show collection.ID

So as you can see when the user own the book I can't have the ID of the book... but I can display the other informations (like the book title) because the other informations are taken from the books table and the ID from the collection table... Too bad that it's not possible to chose the table in the

I understand that the problem is that the ID column exists in both tables. One solution may be to duplicate the ID column, like ID2. Then it will work because ID2 doesn't exist in the collection table. But that is maybe too much...

Thank you for your help.

Regards.

Upvotes: 1

Views: 101

Answers (4)

Peter Bowers
Peter Bowers

Reputation: 3093

It is very easy to set columns up with aliases:

SELECT books.ID AS book_id, collections.id AS collection_id 
FROM books 
LEFT JOIN collection ON books.ID = collection.ID_book AND collection.ID_member = :ID_member 
WHERE books.ID_author = :ID_author

After you have done this your column names will be book_id and collection_id and since they are no longer duplicate names (with one overwriting the other) you can access each of them.

Then if you know that you want all the columns from books but only the idcolumn from collection you can do something like this:

SELECT books.*, collections.id AS collection_id 
FROM books 
LEFT JOIN collection ON books.ID = collection.ID_book AND collection.ID_member = :ID_member 
WHERE books.ID_author = :ID_author

Upvotes: 1

EternalHour
EternalHour

Reputation: 8621

I think you can solve this problem by changing your query slightly, maybe give this a try.

SELECT * FROM books 
LEFT JOIN collection ON books.ID = collection.ID_book
WHERE books.ID_author = :ID_author OR collection.ID_member = :ID_member

When joining on the condition of the user id, you're only going to have those rows available in the result set. By moving that condition to the WHERE clause using the OR operator, you can have both conditions met.

NOTE: Try not to use SELECT *.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269873

I think your query should be doing what you want. I suspect that the problem may be the select * and multiple columns with the same name. Does this query do what you want?

SELECT b.*,
       (case when c.ID_book is not null then 'GREEN' else 'NOT OWNED' end) as color
FROM books b LEFT JOIN
     collection c
     ON b.ID = c.ID_book AND c.ID_member = :ID_member
WHERE b.ID_author = :ID_author;

Upvotes: 1

Sam Teng Wong
Sam Teng Wong

Reputation: 2439

    SELECT * FROM books LEFT JOIN collection
    ON books.ID = collection.ID_book 
    INNER JOIN member ON collection.ID_member = member.:ID_member WHERE books.ID_author = :ID_author

would you try that sir.. I'm not sure though. I'm assuming that the ID_member fields came from a member table.. and the ID_member to collection table is a foreign key..

Upvotes: 0

Related Questions