针对这种情况，SQL中加入了多个表 [英] Multiple tables join in SQL for this scenario

问题描述

这是我的表结构 我有3张桌子:

This is my table structure I have 3 tables:

1. 成员表
2. 评论表
3. 类似表格的评论

表结构可以在以下图像中找到:

The tables structures can be found in the following images:

表:成员

--------------------------------------------------------------------
user_id |full_name |email   | password  | image     |join_date     |
        |          |        |           |           |            |
---------------------------------------------------------------------

表:Album_comments

Table: album_comments

--------------------------------------------------------------------
id |album_id |comment_text | comment_userid | post_date |active_bit |
   |         |             |                |           |     |
---------------------------------------------------------------------

表格:comment_likes

Table: comment_likes

--------------------------------------------------------------------
id |user_id  |comment_id   | post_date      |           | like_bit  |
   |         |             |                |           |     |
---------------------------------------------------------------------

我想联接三个表并检索结果.这是我需要的:

I want to join three tables and retrieve the result. Here is what I need:

我想返回最新的20条评论，并检查当前登录的成员是否喜欢这20条评论中的任何评论.如果是，则将这些注释的状态位返回1，否则返回状态位0.

I want to return latest 20 comments and check if the member who is currently logged in has liked any of the comment in these 20 comments. If yes then return status bit as 1 for those comments and if not then return status bit 0.

谁能告诉我对此进行的SQL查询是什么?

Can anyone tell what would be the SQL query for this?

推荐答案

以下查询应为您工作.从album_comments中选择所需的列，并基于comment_id将其与comment_likes结合起来，并检查comment_likes user_id是否等于您从UI发送的user_id. ORDER BY DESC将返回最新评论，其上限为20.

The following query should work for you. Select required columns from album_comments join it with comment_likes based on comment_id and check if the comment_likes user_id is equal to the user_id you sent from UI. ORDER BY DESC will return latest comments with LIMIT of 20.

Select |ac.Column1, ac.Column2...ac.Column-n|, cl.like_bit
FROM album_comments ac INNER JOIN comment_likes cl
ON ac.id = cl.comment_id AND cl.user_id = |screen user_id|
ORDER BY ac.id DESC LIMIT 20;

这篇关于针对这种情况，SQL中加入了多个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！