SQL placement join with student, friend, package

Question

Issue:

• You are given three tables: Students, Friends and Packages.

• Students contains two columns: ID and Name.

• Friends contains two columns: ID and Friend_ID (ID of the ONLY best friend).
• Packages contains two columns: ID and Salary (offered salary in $ thousands per month).

Write a query to output the names of those students whose best friends got offered a higher salary than them. Names must be ordered by the salary amount offered to the best friends. It is guaranteed that no two students got same salary offer.

Code:

This is the code that I have come up with but it does not produce correct results. Can anyone let me know why?

select TableA.name 
from
(select s.id,s.name,p.salary from students s inner join packages p on s.id=p.id) TableA,
(select f.id,f.friend_id, p2.salary from friends f inner join packages p2 on f.friend_id=p2.id) TableB
where TableA.id=TableB.id And TableA.salary>TableB.salary
order by TableB.salary desc;

Answer 1

I think in your query you wrote AND TableA.salary < TableB.salary instead of AND TableA.salary > TableB.salary. Moreover I think your query can be written in a more synthetic way. On MSSQL (but it works on MYSQL too, as query is very basic), you can try to use this one:

SELECT s.id
        ,s.NAME
        ,p.salary
        , f.friend_id, p2.salary as friend_salary
    FROM students s
    INNER JOIN packages p ON s.id = p.id
    LEFT JOIN friends f ON f.id = s.id
    LEFT JOIN packages p2 ON f.friend_id = p2.id
 WHERE p.salary <= p2.salary
ORDER BY s.id;

Output:

    id  NAME    salary  friend_id   friend_salary
    1   John    1000    2           1200
    3   Pete    800     1           1000

Sample data:

CREATE TABLE students (id int, NAME VARCHAR(30));
CREATE TABLE packages (id int, salary INT);
CREATE TABLE friends (id int, friend_id INT);
INSERT INTO students values (1,'John');
INSERT INTO students values (2,'Arthur');
INSERT INTO students values (3,'Pete');

INSERT INTO packages values (1,1000);
INSERT INTO packages values (2,1200);
INSERT INTO packages values (3,800);

INSERT INTO friends values (1,2);
INSERT INTO friends values (2,3);
INSERT INTO friends values (3,1);

Answer 2

---

you have written 'where TableA.salary>TableB.salary' implying that you want to find rows where your salary is > than your friends. But the question asked was the opposite (to find names where the firends salary is > than your salary) so you can change that to 'where TableB.salary>TableA.salary' and it should work.

select my_name from
(select s.id as my_id,s.name my_name,p.salary as my_salary from students s 
inner join packages p on s.id=p.id) as my_tbl inner join (select f.id as 
id,f.friend_id as frnd_id,p.salary as frnd_salary from friends f inner join 
packages p on f.friend_id=p.id ) as frnd_tbl on my_id=id where 
frnd_salary>my_salary order by frnd_salary;

    

Answer 3

I used CTE for easy code readability. I am not sure whether it is fully optimized or not. But, it yields the result as expected from the question.

with std_salary as (
    SELECT s.id, s.name, p.salary 
    FROM Students s
    JOIN Packages p
    ON s.id=p.id),

    friend_salary as (
    SELECT f.id, p.salary 
    FROM Friends f
    JOIN Packages p
    ON f.friend_id=p.id
    )

SELECT name 
FROM
    (SELECT std_salary.name, std_salary.salary as own, friend_salary.salary as friend
    FROM std_salary
    JOIN friend_salary
    ON std_salary.id=friend_salary.id) as final
WHERE final.own<final.friend
ORDER BY final.friend;

Answer 4

This worked for me in MySql -

select name from Students
inner join Friends on Friends.ID = Students.ID
inner join Packages P1 on P1.ID = Students.ID
inner join Packages P2 on P2.ID = Friends.Friend_ID
where P2.salary > P1.salary
order by P2.salary;

Answer 5

Well using self join we can solve this and similar problems easily

SELECT s.name from students AS s
JOIN friends AS f
ON s.id=f.id 
JOIN packages AS p1
on s.id=p1.id
JOIN packages AS p2
ON p2.id=f.friend_id
WHERE p1.salary<=p2.salary
ORDER BY p2.salary

For better understanding go through SELF JOIN TOPIC IN SQL

Answer 6

This worked for me in Oracle:

  select s.name
    from students s, friends f, packages p1, packages p2 
   where s.id = f.id 
     and s.id = p1.id 
     and p2.id = f.friend_id 
     and p1.salary < p2.salary 
order by p2.salary;

Answer 7

This worked for me in MS SQL

SELECT a.name 
FROM (SELECT students.id as main_id, students.name, packages.salary 
      FROM students join packages on students.id = packages.id) a 
JOIN (SELECT f.id as main_id1, p.salary 
FROM friends f JOIN packages p ON f.friend_id = p.id) b 
ON a.main_id = b.main_id1 
WHERE b.salary>a.salary 
ORDER BY b.salary ASC;

Answer 8

WITH CTE AS
(SELECT s.name, p.salary, f.friend_id
FROM Students s
JOIN Friends f
ON s.id = f.id
JOIN Packages p
ON s.id = p.id)

SELECT c.name FROM CTE c
JOIN students s1
ON s1.id = c.friend_id
JOIN packages p1
ON c.friend_id = p1.id
WHERE c.salary < p1.salary
ORDER BY p1.salary;

Answer 9

select s_name from
    (select s.id as s_id, s.name as s_name, p.salary as s_salary 
            from    
                    Students s
                Join
                    Packages p
            on s.id = p.id) a
    Join
        (select f.id as s_id,friend_id as f_id, s.name as f_name, p.salary as f_salary 
            from    
                    friends f
                Join
                    Packages p
                Join
                    Students s
            on friend_id = p.id
            and 
                s.id = friend_id)b
    ON
        a.s_id = b.s_id
    where s_salary < f_salary
    order by f_salary