Multiple tables join in SQL for this scenario

This is my table structure I have 3 tables:

  1. member table
  2. comments table
  3. comments like table

The tables structures can be found in the following images:

Table: member

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

Table: album_comments

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

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:

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.

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

Answers


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;

Need Your Help

User control attributes at design-time

css visual-studio-2008 user-controls design-time

I'm testing a simple User Control in Visual Studio 2008: A Panel named Wrapper with some controls inside. Can Visual Studio handle this at design time?

Load UIViewController inside a Container View using StoryBoard

ios objective-c xcode cocoa-touch

I have a UIViewController called RootViewController and A UIViewController called NewsViewController. I want to show NewsViewController inside a UIView (the UIView is just a part of the screen) I