Geek asks and answers

Using left join restriction in mysql

In the next query, all messages and each message owner, all comments belonging to each message, and the owner of each comment are selected.

I only need to get 5 comments per post. I rewrote the request, but I get the error message "each view must have its own alias."

SELECT posts.id AS postId, posts.body, users.id AS userId, users.displayname, comments.id AS commentId, comments.text, commenters.id, commenters.displayname
FROM posts
JOIN users ON posts.owneruserid = users.id
LEFT JOIN comments ON posts.id = comments.postid
    JOIN users AS commenters ON comments.userId = commenters.id
ORDER BY posts.createdAt

New request:

SELECT posts.id AS postId, posts.body, users.id AS userId, users.displayname
FROM posts
JOIN users ON posts.owneruserid = users.id
LEFT JOIN (
    SELECT comments.id AS commentId, comments.text AS commentText, commenters.id AS commenterId, commenters.displayname AS commenterDisplayName
    FROM comments
    JOIN users AS commenters ON comments.userid = commenters.id
    LIMIT 0,5
        ) AS comments ON comments.postid = posts.id
ORDER BY posts.createdAt

UPDATE Now the query works, but it does not give the desired result. I want to display 10 posts with 5 comments for each post. This restriction applies only to the comments of the first message found.

0
source share
4 answers

, , , , ... , , . . , sql (@variables), @varRow , reset 1 (, PreQuery ID FIRST). , HAVING, @varRow count < 6 MOST 5 .

, , WHERE (, /, ) INNER, "PreQuery".

select straight_join
      PreQuery.*,
      @varRow := if( @LastPost = PreQuery.PostID, @varRow +1, 1 ) CommentRow,
      @LastPost := PreQuery.PostID PostID2
   from
      ( select
              posts.id PostID,
              posts.body,
              posts.CreatedAt,
              u1.id UserID,
              u1.DisplayName NameOfPoster,
              c.id,
              c.userid CommentUserID,
              c.text CommentText,
              u2.DisplayName CommentUserName
           from
              posts
                 join users u1
                    on posts.ownerUserID = u1.id

                 LEFT JOIN comments c
                    on posts.id = c.PostID

                    join users u2
                       on c.userid = u2.id 
            where
                  posts.id = TheOneParentIDYouWant
               OR posts.parentid = TheOneParentIDYouWant
            order by
               posts.ID,
               c.id desc ) PreQuery,

      (select @varRow := 0, @LastPost = 0 ) SQLVars

   having
      CommentRow < 6   

   order by
      PreQuery.postid,
      CommentRow

--- EDIT --- , , " " , . / , ...

Post -> User (to get posting user name )
Post -> Comment (on Common Post ID -- left joined)
        Comment -> User ( to get commenting user name)

, , , @vars . HAVING , 5, .

+1

:

SELECT posts.id AS postId, posts.body, users.id AS userId, users.displayname
FROM posts
JOIN users ON posts.owneruserid = users.id
LEFT JOIN (
        SELECT comments.id AS commentId, comments.text AS commentText, commenters.id AS commenterId, commenters.displayname AS commenterDisplayName
        FROM comments
        JOIN users AS commenters ON comments.userid = commenters.id
        LIMIT 0,5
    ) AS derived_table_alias
ORDER BY posts.createdAt
+1

( " " ), . , , , :

SELECT posts.id AS postId, posts.body, users.id AS userId, users.displayname
FROM posts
JOIN users ON posts.owneruserid = users.id
LEFT JOIN (
    SELECT comments.id AS commentId, comments.text AS commentText, commenters.id AS commenterId, commenters.displayname AS commenterDisplayName
    FROM comments
    JOIN users AS commenters ON comments.userid = commenters.id
    LIMIT 0,5
) as some_alias --This is what triggering the error
ORDER BY posts.createdAt

, .

+1

  • , .
    : SELECT * FROM foo JOIN (select * from bar) AS <alias_here>

  • , posts createdAt. , MySQL , createdAt ( "A" )

  • LEFT JOIN s, ON. - , . :

    SELECT *   
FROM foo JOIN bar ON (foo.id=bar.id) 
LEFT JOIN (select * from foobar) AS baz **ON foo.id=baz.id**

  • To join a field, the field must be present in the table participating in the join. So, in the above example, if you match foo.idwith baz.id, in the subquery (baz) you need to return id.

+1
source

More articles:


All Articles