CODEWITHSUNDEEP
mysqldistinct
Osakr
Osakr

Reputation: 1066

Getting duplicated records in MySQL trying to get data from two different tables

I am doing a MySQL query which has to return all records from a table and add dynamically two columns, one is taking the value from other table and the last value is evaluating if a record exists in other table and returning true or false depending on it.

Let me explain a little bit better:

I have a table which stores information about books ( bookid, bookname, bookdescription ) there aren't any duplicated record in this table. I have a second table which stores a relation between users and those books ( it stores the reference to the book, the reference to the user ID, if this book is default and the rating of the book )

This is how my query looks like right now:

SELECT DISTINCT b.*,
  IF(ub.ub_bookid is not NULL AND ub.ub_userid = :userid , ub.ub_default, 0) AS "default",
  IF(ub.ub_bookid is not NULL AND ub.ub_userid = :userid , True, False) AS "visible"
FROM `books` as b LEFT JOIN `users_books` as ub
ON ub.ub_bookid = b.b_id;

(Since I am doing the query from PHP with PDO the value :userid is given at the execution time)

The above query pretends to return all books from the book table, and for each book see if in the table users_books there is a record with the given userid, in case that it exists it will return the value of default and return a true in the "active" column. In the case that the record doesn't exists it will return a false has default and false as "active".

My Problem: When I have more than 2 relations with the same book but different users the query is returning duplicated entries. If I remove the DISTINCT the query returns duplicated entries if there is more than one record for the same book ( but obviously different user ids ).

I am not really sure how to return only as many records as the books table has and avoid duplicating them. I've been trying to use GROUP BY in order to achieve this but couldn't do it since the query fails when I use it.

Any advice or help will be so appreciated. Thanks so much.

Upvotes: 0

Views: 35

Answers (2)

Adam
Adam

Reputation: 1142

Something along those lines (Not sure I completely understood what you want)?

SELECT b.*,
    IF(ub.ub_bookid IS NOT NULL, ub.ub_default, 0) AS 'default',
    IF(ub.ub_bookid IS NOT NULL, True, False) AS 'visible'
FROM books b
LEFT JOIN users_books ub ON b.b_id = ub.ub_bookid AND ub.ub_userid = :userid

Upvotes: 1

Osakr
Osakr

Reputation: 1066

Well after some hours of work, I posted this and in 8 minutes testing with phpmyadmin I finally found a way of avoiding the duplicated records. I only needed to add and AND at the JOIN ON (which I didn't know that was possible). Now my query looks like this:

SELECT DISTINCT b.*,
  IF(ub.ub_bookid is not NULL AND ub.ub_userid = :userid , ub.ub_default, 0) AS "default",
  IF(ub.ub_bookid is not NULL AND ub.ub_userid = :userid , True, False) AS "visible"
FROM `books` as b LEFT JOIN `users_books` as ub
ON ub.ub_bookid = b.b_id AND ub.ub_userid = :userid;

Upvotes: 0

Related Questions