Mysql Nested Select

Following on from this question last_question with this table

`id`, `bbs_id`, `user_id`, `like_dislike`
(15,   4,        2,         0),
(14,   4,        1,         0),
(13,   3,        1,         0),
(12,   2,        1,         1),
(11,   1,        2,         0),
(10,   1,        1,        开发者_C百科 1);

How can I see what an individual users like or dislike was? Lets say I wanted to have an aggregate table of all the likes and dislikes with another column for whether user x liked it.

This is the query that I have tried

$user_id = 1;


SELECT bbs_id,
     (SELECT like_dislike FROM bb_ratings WHERE user_id={$user_id}) AS thisUsersRating,
       SUM(CASE WHEN like_dislike = 1 THEN 1 ELSE 0 END) AS likes, 
       SUM(CASE WHEN like_dislike = 0 THEN 1 ELSE 0 END) AS dislikes
FROM bb_ratings
GROUP BY bbs_id

I guess the problem I am running into here is, how do you refer to user_id = x in this particular row, not in all the rows. Thanks in Advance Andrew

---

I have added an answer to the previous question, please refer to that first, for understanding.

You can't get it from bb_ratings alone by GROUPing it and hacking it. You get Null because you are thinking in terms of a grid, not relational sets (that is the central concept of the Relational Model).

1. Before you decide which table(s) to go to, to service your query, you need to decide what you want for the structure of your result set.

2. Then constraint it (which rows) with the WHERE clause.

3. Then figure out where (what tables) to get the columns from. Either joins to more tables, and more work on the WHERE clause; or scalar subqueries, correlated to the outer query.

You are not clear about what you want. It looks like you want the same report as the previous question, plus a column for the given users vote. To me, the structure of your result set is a list of bulletins. Well, I can get that from bulletin, no need to go to bulletin_like and then have to GROUP that.

If you think in terms of sets, it is very easy, no need to jump through hoops with materialised views or "nested" queries:

SELECT name AS bulletin, 
    (SELECT COUNT(like) 
        FROM  bulletin_like bl 
        WHERE bl.bulletin_id = b.bulletin_id 
        AND   like = 1
        ) AS like,
    (SELECT COUNT(like) 
        FROM  bulletin_like bl 
        WHERE bl.bulletin_id = b.bulletin_id 
        AND   like = 0
        ) AS dislike,
    (SELECT COUNT(like) 
        FROM  bulletin_like bl 
        WHERE bl.bulletin_id = b.bulletin_id 
        AND   bl.user_id = {$user_d}
        AND   like = 1
        ) AS your_vote
    FROM bulletin b

Responses to Comments

I have the feeling that what you are saying is very important for how I approach SQL

1. Yes, absolutely. If you are willing to learn the right stuff up front, it will:

   • save you all kinds of problems later
   • make your queries more efficient
   • allow you to code faster
     .

2. For now, forget about using result sets as tables (much slower), and temp tables (definitely not required if your database is Normalised). You are much better off querying the tables directly. You need to learn various subjects such as the Relational model; how to use SQL; how not to use SQL so as to avoid problems; etc. I am willing to help you, and stay with you for a while, but I need to know you are willing. It will take a bit of back-and-forth. There is a bit of noise on this site, so I will ignore other comments (until the end) and respond only to yours.

   • stop using GROUP BY, it is seriously hindering your understanding of SQL. If you can't get the report you want without using GROUP BY, ask a question.
     .

3. This posted question. Let me know at which point you got lost, and I will provide more detail from that point forward.

   • For this question, you want a list of bulletins, with likes; dislikes; and this users likes. Is that correct ? Have you tried the code I provided ?
     .
4. Looked at the linked question. It is a mess, and no one has addressed the deeper problem; they have answered the problem on the surface, in isolation. You now have an answer but you do not understand it. That is a very slow way to progress.

---

$user_id = 1

SELECT bbs_id,
       sum(CASE WHEN user_id = {$user_id} THEN like_dislike END) AS thisUsersRating,
       SUM(CASE WHEN like_dislike = 1 THEN 1 ELSE 0 END) AS likes, 
       SUM(CASE WHEN like_dislike = 0 THEN 1 ELSE 0 END) AS dislikes
FROM bb_ratings
GROUP BY bbs_id

Gives me

bbs_id     thisUsersRating  likes         dislikes
    (1,         '1',            '1',             '1'),
    (2,         '1',            '1',             '0'),
    (3,         '0',            '0',             '1'),
    (4,         '0',            '0',             '2');

If I change the user_id to 4 then I get null for all thisUsersRating fields.