Combine data of three tables in SQL

Question

I'm working on a SQL-statement which will involve 3 different tables. Below you can see the structure of them with one example data each.

FOLLOWERS-TABLE: This table contains information about the current user ('user') following another user ('following')
MESSAGES: This table contains information about messages of a certain user
LIKES: This table contains information about who liked other messages. 'idmessage' is the same as the 'id' in the Messages-table

First of all, I wanted to display all the messages of the persons who the user follows. I used this query:

SELECT * 
FROM Messages 
     LEFT JOIN Followers ON Followers.following=Messages.user 
WHERE Followers.user = 'Peter Jackson'

This resulted in this:

It is working. However, in the next step I should add a custom column to it, this must be 'alreadyLiked'. This should check if the user already liked the message. The value must be 'yes' or 'no'. Therefore, information about the Likes-table is needed. I guess I should make another join on 'Likes.idmessage' and 'Messages.id'. But I don't know how to give it a value 'yes' or 'no'. In the end, it should be like this:

On the server-side I use Node.js and the client-side AngularJS.

Set up direct indexes and set up forigen keys will help your database work better and be more efficient. — Mm-Art-In
– Mm-Art-In, Commented Jan 3, 2018 at 19:57
Possible duplicate of setting flag value base on record exist in another table — Mm-Art-In
– Mm-Art-In, Commented Jan 3, 2018 at 19:58
Please read the duplicate linked post, this is an exact duplicate of that issue. aswer: Set a flag in the SQL. — Mm-Art-In
– Mm-Art-In, Commented Jan 3, 2018 at 19:59
I can't figure out what the syntax of that solution is with 3 tables. And where do the 'c' and 'd' come from? — Soccerrlife
– Soccerrlife, Commented Jan 3, 2018 at 20:15

isaace · Accepted Answer · 2018-01-03 20:41:37Z

1

If I understood correctly then this should work:

SELECT Messages.*,  Followers.*, 
CASE WHEN Likes.id IS NOT NULL THEN 'yes' ELSE 'no' END AS alreadyLiked
FROM Messages 
LEFT JOIN Followers ON Followers.following=Messages.user 
LEFT JOIN Likes ON Likes.idmessage = Messages.id
WHERE Followers.user = 'Peter Jackson'

The way this works is simply by left joining the Likes table. Now if there is a no link found between the message table and the like table then the fields from the Like table will be null. Then we use the case operator to check each row if any field from the like table is or isn't null.

edited Jan 3, 2018 at 20:41

answered Jan 3, 2018 at 20:24

isaace

3,4291 gold badge11 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Combine data of three tables in SQL

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related