SQL placement join with student, friend, package

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;

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);

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;

SQL placement join with student, friend, package

Related

Recent Posts