Postgres SQL query with table relations

Asked

Viewed 48 times

-1

I have a users table with the following fields:

id  
username
email
password
created_at
updated_at

A table followings with fields :

id
user_id 
contact_id  
created_at  
updated_at

And a table with the fields:

id  
user_id 
content 
created_at  
updated_at

Where Users relates to the tweets table, and the followings table, so that a user can have multiple tweets, and a user can have multiple followings. Let’s assume I have a user with

id = 1

and this user has followings with

contact_id = 2

and

contact_id = 3

contact_id would be who he is following, in this case the

user_id

of the table followings would be equal to

1

for both lines. Users 2 and 3, each have some tweets, so the question is, how would you search for the tweets where they were posted by users who are within my followings table? how the query query would look for such?

postgresql

1 answer

0

Assuming your last table is the table tweets, from what I understood of your doubt:

SELECT * FROM folowings INNER JOIN tweets ON folowings.contact_id = tweets.user_id;

If you have misunderstood your doubt then explain it better.

  • But where would I have to pass the user id on which I want my followings tweets?

  • If what you want is the specific data of one, or more than one, user then add a clause WHERE id_user = usuário_desejado. But surely it cannot be deduced that you want this with what you put in your question.

  • In short, I am user 1, and I want to fetch the tweets of all users who are in my following, let’s assume, I have users 2 and 3 in my following, so if I were to search manually ,would select * from tweets Where user_id in (2,3); but I wanted to do this automatically. type select * from tweets Where user_id in (all my followings); but I didn’t want to first search my followings and then search your tweets. wanted to do a single query

  • Only use the JOIN operator.

Browser other questions tagged postgresql

You are not signed in. Login or sign up in order to post.