# John Lekberg

## JUNTO Practice - "Placements"

Discussed on 2019-09-18.

Hint: Join the `Packages` table to the `Friends` table twice, then compare salaries.

### Solution - Daniel

``````select s.name
from students s
inner join friends f on (s.id=f.id)
inner join packages p on (s.id=p.id)
where (select p.salary from packages p where p.id=f.friend_id)
>
(select p.salary from packages p where s.id=p.id)
order by (select p.salary from Packages p where p.id = f.friend_id);
``````

Dan, I reformatted your code a bit to look better on the web page.

You don't need the parentheses on the join conditions `(s.id=f.id)` and `(s.id=p.id)`. Studying joins more would probably be helpful. I recommend a book called "The Language of SQL" by Larry Rockoff, which has a few chapters on joins.

-- John

### Solution - John

``````  SELECT Students.name
FROM Packages P1
INNER JOIN Friends
ON Friends.id = P1.id
INNER JOIN Packages P2
ON P2.id = Friends.friend_id
INNER JOIN Students
ON Students.id = P1.id
WHERE P2.salary > P1.salary
ORDER BY P2.salary
;
``````

Looks pretty concise.

-- Daniel

Using table names like "Package_Me" and "Package_Friend" could be more readable than "P1" and "P2". Here's a diagram showing how SQL joins work. As far as query performance, try using an `EXPLAIN` statement (SQLite, MySQL, ...).

-- John

### Solution - Oscar

``````SELECT
S.Name
FROM
Students AS S
LEFT JOIN
Packages AS P
ON S.ID = P.ID
LEFT JOIN
(
SELECT
Friends.ID,
Packages.Salary
FROM
Friends
LEFT JOIN
Packages
ON Friends.Friend_ID = Packages.ID
) AS F
ON S.ID = F.ID
WHERE P.Salary < F.Salary
ORDER BY F.Salary ASC
;
``````

Oscar, I fixed the formatting because your email mangled it.

Code could be clearer if you didn't use a subquery and instead joined on all the tables. The subquery produces a table of a student's ID and their friend's salary, which Daniel and I found a bit confusing. For this data, I don't think there's a difference between `LEFT JOIN` and `INNER JOIN`, but this problem is better solved with an inner join, because a left outer join might introduce `NULL` in attributes selected from the `F` and `P` tables. `ASC` is the default in an `ORDER BY` clause, so you can omit it (but it might be clearer to you to not).

-- John

Avoid the subquery if you can.

-- Daniel