CODEWITHSUNDEEP
mysqlsqldatabasejoin
Nikhil vyas
Nikhil vyas

Reputation: 37

SQL - inner join on different criteria

Just getting confused on basic stuff -

could someone explain me this - 

select s.name from students s
inner join friends f on f.id = s.id
inner join packages p on p.id = s.id
where p.salary < (select pp.salary from packages pp where pp.id = f.friend_id)
order by (select pp.salary from packages pp where pp.id = f.friend_id) ASC;

the salary comparison part - i.e select pp.salary from packages pp where pp.id = f.friend_id should not yield the same salary result? - so how can we compare.

for references, use the below sample tables

table 1- students columns - id, name

table 2 - friends (here each id is linked with one friend_id (his best friend)) columns - id , friend_id

table3 - packages columns - id , salary

Trying to find out the name of the friend whose best friend's salary is more than his salary.

I am confused at understanding this solution.

Upvotes: 0

Views: 143

Answers (1)

Rahul
Rahul

Reputation: 77866

That where subquery part is wrong cause the subquery will return multiple record and which can't be used with < operator since it's accepts scalar value. Rather change that to a JOIN as well like

JOIN packages pp ON pp.id = f.friend_id
AND p.salary < pp.salary

Change your query to be

select s.name from students s
inner join friends f on f.id = s.id
inner join packages p on p.id = s.id
JOIN packages pp ON pp.id = f.friend_id
AND p.salary < pp.salary
order by pp.salary;

Upvotes: 1

Related Questions