Help With The Syntax Of This SQL QUERY

Question

Hi,

I'm inexperienced with SQL and I'm not sure how to form this query correctly.

Select comment.text, (COUNT(parent.id)-1) AS depth 
                                      FROM comments AS comment, 
                                           comments AS parent
                                      WHERE comment.lft BETWEEN parent.lft AND 
parent.rght LEFT JOIN users as user ON (comment.user_id = user.id)
                                          GROUP BY comment.id ORDER BY comment.lft

I'm trying to retrieve nested user comments stored in a MySQL database. Along with each comment I'm also trying to retrieve the comment's associated user with a JOIN but I'm not sure what the syntax would be for something like this.

Answer 1

You can't have joins after a WHERE clause. Try this instead:

SELECT comment.text, (COUNT(parent.id)-1) AS depth 
FROM comments AS comment
JOIN comments AS parent
    ON comment.lft BETWEEN parent.lft AND parent.rght
LEFT JOIN users as user
    ON comment.user_id = user.id
GROUP BY comment.id ORDER BY comment.lft

Also presumably you have joined with the user table in order to select the user name of the user that posted the comment? Then you need to select that column too. Add user.username as an extra column in your select statement and you'll get a result like this:

'ELECTRONICS', 0, 'Foo'
'TELEVISIONS', 1, 'Foo'
'TUBE', 2, 'Foo'
'LCD', 2, 'Bar'
'PLASMA', 2, 'Foo'
'PORTABLE ELECTRONICS', 1, 'Bar'
'MP3 PLAYERS', 2, 'Foo'
'FLASH', 3, 'Foo'
'CD PLAYERS', 2, 'Foo'
'2 WAY RADIOS', 2, 'Foo'

Test data used to test this (modified from here):

CREATE TABLE comments (id INT NOT NULL, user_id INT NOT NULL, text NVARCHAR(100) NOT NULL, lft INT NOT NULL, rght INT NOT NULL);
INSERT INTO comments (id, user_id, text, lft, rght) VALUES
(1, 1, 'ELECTRONICS', 1, 20),
(2, 1, 'TELEVISIONS', 2, 9),
(3, 1, 'TUBE', 3, 4),
(4, 2, 'LCD', 5, 6),
(5, 1, 'PLASMA', 7, 8),
(6, 2, 'PORTABLE ELECTRONICS', 10, 19),
(7, 1, 'MP3 PLAYERS', 11, 14),
(8, 1, 'FLASH', 12, 13),
(9, 1, 'CD PLAYERS', 15, 16),
(10, 1, '2 WAY RADIOS', 17, 18);

CREATE TABLE Users (id INT NOT NULL, username NVARCHAR(100) NOT NULL);
INSERT INTO Users (id, username) VALUES
(1, 'Foo'),
(2, 'Bar');