Linking an inner join table with a column other than the primary key

Question

The Problem statement is something like this

You are given two tables.

1.Person - Details of people present.

2.Friend - Relation between two indivisuals with given IDs.(An indivisual with PersonID has friend with FriendID,not necessarily vice versa.)

Table Description:

Table Name: Person

Table Columns: PersonID,Name,Email,Score

PersonID: Unique ID of each person

Name: Name of each person

Email: Email of each person

Score: Score of each person.

Table Name: Friend

Tables Columns: PersonID,FriendID

PersonID: ID of a person

FriendID: ID of friend

You have to output a table containing PersonID,Name,number of friends,sum of marks of all friends of those individuals who have friends with total score greater than 100.The table should be sorted in increasing order of PersonID.All pairs in Friend table (PersonID,FriendID) are distinct.

Answer 1

you need to join sample person table and sample friend table and then join the friend table with sample person table to get the marks

select p.personid,p.name,count(1) as number_of_friends, sum(p_f.score) from person p
join friend f on p.personid = f.personid 
join person p_f on f.friendid = p.personid 
group by p.personid,p.name
having sum(p_f.score) >100

Answer 2

You could use:

select t1.personid,name,nr_of_friends,sum_of_score
from person p
inner join (select f.personid,count(f.friendid) as nr_of_friends,sum(score)  as sum_of_score
            from friend f
            inner join person p on f.friendid=p.personid
            group by f.personid
            ) as t1 on t1.personid=p.personid
where p.personid in (2,4) ;

or

select t1.personid,name,nr_of_friends,sum_of_score
from person p
inner join (select f.personid,count(f.friendid) as nr_of_friends,sum(score)  as sum_of_score
            from friend f
            inner join person p on f.friendid=p.personid
            group by f.personid
            ) as t1 on t1.personid=p.personid
where sum_of_score >=100;

Check demo for more details