CODEWITHSUNDEEP
sqlsql-serverjoin
Deep_learner
Deep_learner

Reputation: 51

How to order names in a column based on an index sequence in another table?

So I have 3 tables Friends and Students and they both have an ID column. But in the Friends table, there is also a column known as Friend_ID. I want to reorder the Name column in Friends in the way the Friend_ID is ordered. Is it possible?

This is for the problem - https://www.hackerrank.com/challenges/placements/problem?isFullScreen=false

Note: I want to use this without the ORDER BY clause as this will be a subquery.

My query in SQL Server.

WITH 
    CTE (NID,N_Name,N_Salary,FID) AS
    (SELECT S.ID AS NID, S.Name AS N_Name, P.Salary AS N_Salary, F.Friend_ID AS FID 
     FROM Students S JOIN Friends F
     ON S.ID = F.ID JOIN Packages P 
     ON S.ID = P.ID),
     
     
--SELECT L.NID,L.N_Name,L.N_Salary,L.FID,R.FN_Sal
SELECT 
   IIF(L.N_Salary > R.FN_Sal,
       IIF(L.FID IN (SELECT ID FROM Students WHERE Students.ID=R.N_ID),L.N_Name,"No val")
    ,NULL)

FROM CTE L
JOIN 
    (SELECT F.ID AS N_ID, P.Salary AS FN_Sal, F.Friend_ID AS F_ID 
     FROM Friends F JOIN Packages P ON F.Friend_ID = P.ID) R
ON L.NID = R.N_ID

enter image description here

I guess there is something wrong with my query. This is definitely not an elegant solution but I am just trying out an idea. Any input would be really helpful, thanks in advance.

Upvotes: 1

Views: 95

Answers (4)

Deep_learner
Deep_learner

Reputation: 51

Thank you for all your inputs! They are correct and I see how unnecessarily I have complicated such a simple question.

This is the solution I was trying to achieve, according to my logic:

WITH 
    CTE (N_ID,N_Name,N_Salary,FID) AS
    (SELECT S.ID AS N_ID, S.Name AS N_Name, P.Salary AS N_Salary, F.Friend_ID AS FID 
     FROM Students S JOIN Friends F
     ON S.ID = F.ID JOIN Packages P 
     ON S.ID = P.ID)
     
     
SELECT L.N_Name
FROM CTE L
JOIN 
    (SELECT F.ID AS N_ID, P.Salary AS FN_Sal, F.Friend_ID AS F_ID 
     FROM Friends F JOIN Packages P ON F.Friend_ID = P.ID) R
ON L.N_ID = R.N_ID
WHERE R.FN_Sal > L.N_Salary
ORDER BY R.FN_Sal;

Upvotes: 0

forpas
forpas

Reputation: 164089

You must join Students to Friends and to 2 copies of Packages.
The 1st copy of Packages will return the student's salary and the 2nd copy will return the friend's salary:

SELECT s.Name
FROM Students s
INNER JOIN Friends f ON f.ID = s.ID
INNER JOIN Packages ps ON ps.ID = s.ID
INNER JOIN Packages pf ON pf.ID = f.Friend_ID
WHERE pf.Salary > ps.Salary
ORDER BY pf.Salary

See the demo.
Results:

> | Name     |
> | :------- |
> | Samantha |
> | Julia    |
> | Scarlet  |

Upvotes: 1

Andreas Sundström
Andreas Sundström

Reputation: 218

This is one answer to the question:

DECLARE @Friends TABLE (ID int, Friend_ID INT);
DECLARE @Students TABLE (ID int, Name VARCHAR(100));
DECLARE @Packages TABLE (ID int, Salary DECIMAL(4,2));

INSERT INTO @Friends SELECT ID,Friend_ID FROM (VALUES(1,2),(2,3),(3,4),(4,1)) Friends(ID,Friend_ID);
INSERT INTO @Students SELECT ID,[Name] FROM (VALUES(1,'Ashley'),(2,'Samantha'),(3,'Julia'),(4,'Scarlet')) Students(ID,[Name]);
INSERT INTO @Packages SELECT ID,Salary FROM (VALUES (1,15.20),(2,10.06),(3,11.55),(4,12.12)) Packages(ID,Salary);

WITH StudentPackage AS (
    SELECT
        Student.ID,
        Student.[Name] StudentName,
        Packages.Salary,
        BestFriend.Friend_ID 
    FROM @Students Student
    JOIN @Packages Packages
    ON Student.ID = Packages.ID
    JOIN @Friends BestFriend
    ON Student.ID = BestFriend.ID
)
SELECT
    Student.StudentName
FROM StudentPackage Student
JOIN StudentPackage BestFriend
ON Student.Friend_ID = BestFriend.ID AND BestFriend.Salary > Student.Salary
ORDER BY BestFriend.Salary

Upvotes: 1

Anu
Anu

Reputation: 316

Have you considered using the Order by clause. That should work.

Upvotes: 0

Related Questions