Using the count function on third table in two table select statement in MariaDB

Question

I just spent a few hours reading through the MariaDB docs and various questions here trying to figure out a SQL statement that did what I want. I'm definitely not an expert... eventually I did get the result I expected, but I have no idea why it works. I want to be sure I am actually getting the result I want, and it isn't just working for the few test cases I have thrown at it.

I have three tables guestbook, users, and user_likes. I am trying to write a SQL statement that will return the user name and first name from users, post content, post date, post id from guestbook, and a third column likes which is the total number of times that post id from guestbook appears in the user_likes table. It should only return posts which are of type standard and should order the rows by ascending post date.

Sample data:

CREATE TABLE users
    (`user_id` int, `user_first` varchar(6), `user_last` varchar(7),
     `user_email` varchar(26), `user_uname` varchar(6))
;
    
INSERT INTO users
    (`user_id`, `user_first`, `user_last`, `user_email`, `user_uname`)
VALUES
    (0, 'Bob', 'Abc', 'email@example.com', 'user1'),
    (13, 'Larry', 'Abc', 'email@example.com', 'user2'),
    (15, 'Noel', 'Abc', 'email@example.com', 'user3'),
    (16, 'Kate', 'Abc', 'email@example.com', 'user4'),
    (17, 'Walter', 'Sobchak', 'walter.sobchak@shabbus.com', 'Walter'),
    (18, 'Jae', 'Abc', 'email@example.com', 'user5')
;


CREATE TABLE user_likes
    (`user_id` int, `post_id` int, `like_id` int)
;
    
INSERT INTO user_likes
    (`user_id`, `post_id`, `like_id`)
VALUES
    (0, 23, 1),
    (0, 41, 2),
    (13, 23, 7)
;


CREATE TABLE guestbook
    (`post_id` int, `user_id` int, `post_date` datetime,
     `post_content` varchar(27), `post_type` varchar(8),
     `post_level` int, `post_parent` varchar(4))
;
    
INSERT INTO guestbook
    (`post_id`, `user_id`, `post_date`, `post_content`,
     `post_type`, `post_level`, `post_parent`)
VALUES
    (2, 0, '2018-12-15 20:32:40', 'test1', 'testing', 0, NULL),
    (8, 0, '2018-12-16 14:06:40', 'test2', 'testing', 0, NULL),
    (9, 13, '2018-12-16 15:47:55', 'test4', 'testing', 0, NULL),
    (23, 0, '2018-12-25 17:59:46', 'Merry Christmas!', 'standard', 0, NULL),
    (39, 16, '2018-12-26 00:28:04', 'Hello!', 'standard', 0, NULL),
    (40, 15, '2019-01-27 00:46:12', 'Hello 2', 'standard', 0, NULL),
    (41, 18, '2019-02-25 00:44:35', 'What are you doing?', 'standard', 0, NULL)
;

I tried a whole bunch of convoluted statements involving count and couldn't get what I wanted. Through what seems like dumb luck I stumbled into creating this statement which appears to be giving me what I want.

SELECT 
  u.user_uname, u.user_first, g.post_id, g.post_date,
  g.post_content, count(user_likes.post_id) AS likes
FROM
  users AS u, guestbook AS g
LEFT JOIN
  user_likes on g.post_id=user_likes.post_id
WHERE
  u.user_id=g.user_id AND g.post_type='standard'
GROUP BY
  g.post_id
ORDER BY
  g.post_date ASC;

Question: Why does this count function appear to work?

The count function that I was able to get working is this, but it only works for hard coded post_id values.

SELECT COUNT(CASE post_id WHEN 23 THEN 1 ELSE null END) FROM user_likes;

When I try to match the post_id from guestbook table by changing to this I get an incorrect value which appears to be the whole table of user_likes.

SELECT COUNT(case when guestbook.post_id=user_likes.post_id then 1 else null end) FROM guestbook, user_likes;

Adding a GROUP BY guestbook.post_id to the end gets me closer, but now I need to figure out how to combine that with my original select statement.

+----------------------------------------------------------------------------+
| COUNT(case when guestbook.post_id=user_likes.post_id then 1 else null end) |
+----------------------------------------------------------------------------+
|                                                                          0 |
|                                                                          0 |
|                                                                          0 |
|                                                                          2 |
|                                                                          0 |
|                                                                          0 |
|                                                                          1 |
+----------------------------------------------------------------------------+

This is the output I want, which I am getting. I just don't trust that my statement is reliable or correct.

+------------+------------+---------+---------------------+---------------------+-------+
| user_uname | user_first | post_id | post_date           | post_content        | likes |
+------------+------------+---------+---------------------+---------------------+-------+
| user1      | Bob        |      23 | 2018-12-25 17:59:46 | Merry Christmas!    |     2 |
| user4      | Kate       |      39 | 2018-12-26 00:28:04 | Hello!              |     0 |
| user3      | Noel       |      40 | 2019-01-27 00:46:12 | Hello 2             |     0 |
| user5      | Jae        |      41 | 2019-02-25 00:44:35 | What are you doing? |     1 |
+------------+------------+---------+---------------------+---------------------+-------+

Fiddle of statement working: http://sqlfiddle.com/#!9/968656/1/0

user_1766831 · Answer

JOIN + COUNT -- A query first combines the tables as directed by the JOIN and ON clauses. The result is put (at least logically) into a temporary table. Often this temp table has many more rows than any of the tables being JOINed.

Then the COUNT(..) is performed. It is counting the number of rows in that temp table. Maybe that count is exactly what you want, maybe it is a hugely inflated number.

count(user_likes.post_id) has the additional hiccup of not counting any rows where user_likes.post_id IS NULL. That is usually irrelevant, in which case, you should simply say COUNT(*).

Please don't use the commalist form for joining. Always use FROM a JOIN b ON ... where the ON clause says how tables a and b are related. If there is also some filtering, put that into the WHERE clause.

If the COUNT is too big, put aside the query you have developed and start over to develop a query that does exactly one thing -- compute the county. This query will probably use fewer tables.

Then build on that to get any other data you need. It may look something like

SELECT ...
    FROM ( SELECT foo, COUNT(*) AS ct FROM t1 GROUP BY foo ) AS sub1
    JOIN t2 ON t2.foo = sub1.foo
    JOIN t3 ON ...
    WHERE ...

Get that initial query that gets the right COUNT. Then, if needed, come back for more help.

As tried by Bryan

OK, I made a few changes.

SELECT  u.user_uname, u.user_first,
        g2.post_id, g2.post_content, g2.post_date,
        sub.likes
    FROM  
    (
        SELECT  g.post_id,
                SUM(g.post_id = ul.post_id) AS likes
            FROM  guestbook AS g
            JOIN user_likes AS ul
            WHERE  g.post_type = 'standard'
    ) AS sub
    JOIN  guestbook AS g2  ON sub.post_id = g2.post_id
    JOIN  users AS u       ON u.user_id = g2.user_id;

Indexes:

guestbook:  (post_type, post_id)  -- for derived table
guestbook:  (post_id)             -- for outer SELECT
users:  (user_id)
user_likes:  (post_id)

Notes:

ORDER BY removed since it was useless in context.
COUNT..CASE changed to shorter SUM.
JOIN ON used

Since there is only one value coming from the derived table, this might work equally well:

SELECT  u.user_uname, u.user_first,
        g.post_id, g.post_content, g.post_date,
        ( SELECT  COUNT(*)
            FROM user_likes AS ul
            WHERE g.post_id = ul.post_id
        ) AS likes
    FROM  guestbook AS g
    JOIN  users AS u   USING(user_id);
    WHERE g.post_type = 'standard'

This involved lots of changes; see if it looks 'right'. It is now a lot simpler.

Indexes are same as above.

Using the count function on third table in two table select statement in MariaDB

1 Answers