CODEWITHSUNDEEP
mysqlsql
atomty
atomty

Reputation: 187

Fetching data from two tables and joining them together

I have a table called participant_tb, the columns of the table are like this

participant_id : 1, username: 'atom', fullname: 'abc' participant_id: 2, username : 'ion', fullname: 'def' participant_id: 3, username: 'molecule', fullname: 'ghi' participant_id: 4, username: 'compound', fullname: 'jkl'

I have another table that looks like this, the table is called follower_tb

follow_id: 1, follower_id: 1, followed_id: 2, follow_id: 2, follower_id: 2, followed_id: 1, follow_id: 3, follower_id: 3, followed_id: 1, follow_id: 4, follower_id: 1, followed_id: 4.

Once someone clicks on the follow button, the id of the follower will be stored in the follower_id and the id of the person that is followed will be stored in the followed_id columns. What I want to do is this: let's say "atom' with the participant_id of 1 loggedin, I want to fetch the list of those he is following and those following him.

I tried something like this:

SELECT p.participant_id, p.username, p.fullname, f.follower_username, f.followed_username, f.follower_id
FROM my_participants p
JOIN my_followers f ON ( p.participant_id = f.followed_id ) 
WHERE followed_id =  1 || follower_id =  1

The problem I have with this is that, it only fetched those that are following atom.

I also tried this

SELECT p.participant_id, p.username, p.fullname, f.follower_username, f.followed_username, f.follower_id
FROM my_participants p
JOIN my_followers f ON ( p.participant_id = f.follower_id ) 
WHERE followed_id =  1 || follower_id =  1

The problem I have with this is that, it includes atom in its result. Producing something like this

participant_id: 1, username: 'atom', follower_id: 1, followed_id: 2,

participant_id: 2, username: 'ion', follower_id: 1, followed_id: 2,

participant_id: 1, username: 'atom', follower_id: 2, followed_id: 1, participant_id: 2, username: 'ion', follower_id: 2, followed_id: 1, participant_id: 3, username: 'molecule', follower_id: 3, followed_id: 1, participant_id: 1, username: 'atom', follower_id: 3, followed_id: 1, participant_id: 4, username: 'compound', follower_id: 1, followed_id: 4 participant_id: 1, username: 'atoom', follower_id: 1, followed_id: 4

I want something like this:

participant_id: 2, username: 'ion', follower_id: 1, followed_id: 2, participant_id: 2, username: 'ion', follower_id: 2, followed_id: 1, participant_id: 3, username: 'molecule', follower_id: 3, followed_id: 1, participant_id: 4, username: 'compound', follower_id: 1, followed_id: 4

or something like this (this first line shows atom is following this person and the person is following back):

participant_id: 2, username: 'ion', follower_id: 2, followed_id: 2, participant_id: 3, username: 'molecule', follower_id: 3, followed_id: 1, participant_id: 4, username: 'compound', follower_id: 1, followed_id: 4

Upvotes: 1

Views: 123

Answers (3)

atomty
atomty

Reputation: 187

SELECT p.participant_id, p.username, p.fullname, f.follower_username, f.followed_username, f.follower_id FROM my_participants p JOIN my_followers f ON ( p.participant_id = f.follower_id || p.participant_id = f.followed_id ) WHERE p.participant_id !=1 and ( f.followed_id = 1 || f.follower_id = 1)

Upvotes: 0

Nick Reed
Nick Reed

Reputation: 5059

This looks like a job for a SQL Subquery, which lets you use the result(s) of one query as the input for another. As it turns out, the queries to fetch followers and followees are very similar.

#select those FOLLOWING the logged-in account

SELECT p.participant_id, p.username, f.follower_id, f.followed_id FROM participant_tb AS p
JOIN follower_tb AS f ON (p.participant_id = f.follower_id)
WHERE p.participant_id IN (
    #make a sub-query to fetch the follower_id's of the entries that follow participant_id 1

    SELECT f.follower_id
    FROM participant_tb AS p
    JOIN follower_tb AS f ON (p.participant_id = f.followed_id)
    WHERE participant_id = 1 #your value here
);

The subquery picks out each follower_id that is following participant_id 1, and then uses that list of ids to pick the usernames out of participant_tb.

#select those BEING FOLLOWED BY the logged-in account

SELECT p.participant_id, p.username, f.follower_id, f.followed_id FROM participant_tb AS p
JOIN follower_tb AS f ON (p.participant_id = f.followed_id)
WHERE p.participant_id IN (
    #make a sub-query to fetch the follower_id's of the entries that are being followed by participant_id 1

    SELECT f.followed_id
    FROM participant_tb AS p
    JOIN follower_tb AS f ON (p.participant_id = f.follower_id)
    WHERE participant_id = 1 #your value here
);

This subquery is almost identical, but it selects the followed_id of all elements that are being followed by participant_id 1.

The returned values of query 1:

particiapnt_id  username    follower_id followed_id
2               ion         2           1
3               molecule    3           1

The returned values of query 2:

particiapnt_id  username    follower_id followed_id
2               ion         1           2
4               compound    1           4

Upvotes: 1

lakshay Gulati
lakshay Gulati

Reputation: 51

try this one:

SELECT p.participant_id, p.username, p.fullname, f.follower_username, f.followed_username, f.follower_id FROM my_participants p JOIN my_followers f ON ( ( p.participant_id = f.follower_id ) or ( p.participant_id = f.followed_id ))

Upvotes: 0

Related Questions