Select a value inside a select statement

Question

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      |

Yes the two ?s are the userid and the status. I clarified this in a comment to @Ollie Jones' answer. Does this make it clearer? — erdomester, Jul 24 '14 at 18:30
Often it's helpful to use http://sqlfiddle.com/ to prototype these sorts of things. But you presented your sample data as an image and your sample result set as a php dump, so it's hard to experiment. It might help if you present your desired result. — O. Jones, Jul 24 '14 at 19:13
But why not update your question with the desired result !?! — Strawberry, Jul 25 '14 at 16:28

score 1 · Answer 1 · answered Jul 24 '14 at 16:45

1

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

answered Jul 24 '14 at 16:45

O. Jones

103,626
17
118
172

What I am trying to achieve is to list books where I can see how many people added them and whether I added them or not. Does this make it clearer? – erdomester Jul 24 '14 at 18:26
Example: So based on the table above BOOKID 0002 is added by 1 person, completed by 1 person and I added it. BOOKID 0003 is added by 1 person, completed by 0 person and I added it. BOOKID 0005 is added by 2 persons, completed by 0 person and I didn't add it. – erdomester Jul 24 '14 at 18:29
Hi Ollie. I simplified the question and created an sqlfiddle for the problem. Do you understand now what I want to do? This is my personal project (no job or homework) and I have limited sql knowledge (yet). – erdomester Jul 25 '14 at 15:46

O. Jones · Accepted Answer · 2014-07-25T19:02:37.730

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.

Well, I am not sure I understand your code but it works. This is not novice level. Thank your for taking the time to help me! Btw, isn't there an easier way to do this? — erdomester, Jul 25 '14 at 18:27
@erdomester, dang! I hate handing out code that people don't understand. That's not the point of SO. I hope you'll take a little time to try to see how this SQL is structured from two separate virtual tables formulated as subqueries. As to your question, is there an "easier" way? Maybe, but it probably relies on tricks. This way is intended to teach how to construct result sets that come from different aggregates. — O. Jones, Jul 25 '14 at 19:01
I am still processing this. I managed to customize it to my goal but it's not easy. — erdomester, Jul 25 '14 at 19:29

Select a value inside a select statement

2 Answers2