CODEWITHSUNDEEP
phpmysqlpdogroup-byaggregate-functions
erdomester
erdomester

Reputation: 11829

Select a value inside a select statement

I have two tables: BOOKS and USERS_BOOKS:

BOOKS:

| ID | BOOKNAME |
|----|----------|
|  1 |    Book1 |
|  2 |    Book2 |
|  3 |    Book3 |
|  4 |    Book4 |
|  5 |    Book5 |

USERS_BOOKS:

| ID | USERID | BOOKID | STATUS |
|----|--------|--------|--------|
|  1 |    001 |      1 |   Read |
|  2 |    001 |      2 |   Read |
|  3 |    001 |      3 |  Added |
|  4 |    002 |      1 |  Added |
|  5 |    002 |      5 |  Added |
|  6 |    003 |      2 |   Read |
|  7 |    004 |      4 |   Read |

http://sqlfiddle.com/#!2/9cff9/1

From this sqlfiddle I can query a list of books and the number people who read them.

     select BOOKS.ID, BOOKS.BOOKNAME, 
            SUM(CASE WHEN USERS_BOOKS.STATUS='Read' THEN 1 ELSE 0 END) AS NUM_READ
       from BOOKS  
  LEFT JOIN USERS_BOOKS ON USERS_BOOKS.BOOKID = BOOKS.ID 
   GROUP BY BOOKS.ID

http://sqlfiddle.com/#!2/9cff9/1

What I need to add to this query is that I want to see if a specific user (USERID 001) read these books or not. So in a fourth column I want to display that I READ the first book (YES), READ the second book, NOTREAD the third, the fourth and the fifth books either (NO). (One person read the fourth book but that was not me).

DESIRED RESULT:

| ID | BOOKNAME| NUM_READ | DID_I_READ_IT|
|----|---------|----------|--------------|
|  1 |  BOOK1  |    1     |      YES     |
|  2 |  BOOK2  |    2     |      YES     |
|  3 |  BOOK3  |    0     |      NO      |
|  4 |  BOOK4  |    1     |      NO      |
|  5 |  BOOK5  |    0     |      NO      |

Upvotes: 2

Views: 67

Answers (2)

O. Jones
O. Jones

Reputation: 108641

Thank you for clarifying your question.

First, let's consider the query that will figure out whether a particular user ("001" for example) read a particular book. This query (http://sqlfiddle.com/#!2/9cff9/13/0) gives a list of users with the books they have read at least once.

   select DISTINCT USERID, 
                   BOOKID
     FROM USERS_BOOKS
    WHERE STATUS = 'Read'
      AND USERID='001'

Now, you need to join this query to the one you have and choose just a particular user. The knack here is to realize that a query / subquery / virtual table and a physical table can be used interchangeably.

So you get this compound query (http://sqlfiddle.com/#!2/9cff9/19/0) to give you what you want. It's a little complex because you're taking two distinct book-by-book aggregates of your data and joining them together. When the aggregates are different, you need to compute them with different subqueries.

   SELECT a.ID, a.BOOKNAME, a.NUM_READ, 
          CASE WHEN b.BOOKID IS NULL THEN 'NOTREAD' ELSE 'READ' END AS DID_I_READ_IT
     FROM ( /* first subquery */
            select BOOKS.ID, BOOKS.BOOKNAME, 
                   SUM(CASE WHEN USERS_BOOKS.STATUS='Read' THEN 1 ELSE 0 END) AS NUM_READ
              from BOOKS
         LEFT JOIN USERS_BOOKS ON USERS_BOOKS.BOOKID = BOOKS.ID 
          GROUP BY BOOKS.ID, BOOKS.NAME
          ) AS a
LEFT JOIN ( /* second subquery */
            select DISTINCT USERID, 
                            BOOKID
                       FROM USERS_BOOKS
                      WHERE STATUS = 'Read'
                        AND USERID = '001'
         ) AS b ON a.ID = b.BOOKID

Notice, if you will, the LEFT JOIN/IS NULL pattern for generating the 'NOTREAD' status yous specification calls for.

Notice, also, that you're STILL misusing GROUP BY. I fixed it in my answer.

Upvotes: 0

O. Jones
O. Jones

Reputation: 108641

You are misusing GROUP BY and a pernicious misfeature of MySQL is confusing you. Read this: http://dev.mysql.com/doc/refman/5.5/en/group-by-extensions.html

Use GROUP BY in a standard way, and your results will be a lot more predictable. Each item in your SELECT clause must, if you are using standard GROUP BY, be also either named in your GROUP BY clause or be in an aggregate function like SUM().

Your DID_I_ADD_IT column in your result set is not that. I don't completely understand what you're trying to do, but you might try changing that column's code to this:

   GROUP_CONCAT(DISTINCT CASE WHEN USERS_BOOKS.USERID=? AND USERS_BOOKS.STATUS = ? 
                              THEN 'Yes' ELSE 'No' END) AS DID_I_ADD_IT

That will move that data into an aggregate function.

You should also change your GROUP BY to this:

 GROUP BY BOOKLIST.ID, BOOKLIST.BOOK_NAME

Upvotes: 1

Related Questions