with all_friends as (SELECT s.Name, f.ID, f.Friend_ID, p.Salary AS person_salary, pg.Salary AS friend_salary FROM Students s INNER JOIN Friends f ON s.ID = f.ID INNER JOIN Packages p ON s.ID = p.ID INNER JOIN Packages pg ON f.Friend_ID = pg.ID friend's salary WHERE pg.Salary > p.Salary ORDER BY pg.Salary ASC) SELECT Name FROM all_friends;

WITH FriendsSalary AS ( SELECT s.ID ,p.Salary FROM Students s JOIN Friends f ON f.ID=s.ID JOIN Packages p ON p.ID=f.Friend_ID ), StudentsSalary AS ( SELECT s.ID ,s.Name,p.Salary FROM Students s JOIN Friends f ON f.ID=s.ID JOIN Packages p ON p.ID=s.ID ) SELECT s.Name from StudentsSalary s JOIN FriendsSalary f ON f.ID=s.ID where f.Salary>s.Salary ORDER BY f.Salary

ngoccth_SQL SERVER: WITH table_1 AS ( SELECT fr.ID, friend_id, name, salary AS salary_id FROM Friends AS fr JOIN Students AS st ON fr.id = st.id JOIN Packages AS pa ON pa.id = fr.id ) SELECT name FROM table_1 JOIN Packages AS pa ON pa.id = table_1.friend_id WHERE salary_id < pa.salary ORDER BY pa.salary

with fnd as( Select st.ID as ID, st.Name as Name, pk.Salary as Salary, f.Friend_ID, st1.Name as bf_name, pk1.Salary as bf_Sal from Students st join Packages pk on st.ID = pk.ID --- to get the student salary join Friends f on st.ID = f.ID --- to get the best Friend ID join Students st1 on st1.ID = f.Friend_ID -- to get the best friend Name join Packages pk1 on f.Friend_ID = pk1.ID --- to get the best friend Salary )

Select Name from fnd where Salary < bf_Sal order by bf_Sal

--- Name(output) < BestFriend (Salary offered) --- order by best Salary asc

What's wrong with below?:

with s as ( select * from students inner join packages using(id)), f as ( select * from friends inner join packages using(id) ) select s.name from s inner join f on s.id = f.id where s.salary < f.salary order by f.salary;