Lucky Makhado Lucky Makhado - 3 months ago 22
MySQL Question

Explaining sql server code

Can someone explain this code for me? It outputs the difference between each presenter (PNRFNAME) salary (Salary_yearly)

SELECT P2.PNRFNAME ,P1.PNRFNAME AS "pnrfname" , P2.SALARY_YEARLY - P1.SALARY_YEARLY AS "salary difference"
FROM PRESENTERS P2
JOIN PRESENTERS P1
ON P2.PNRFNAME < P1.PNRFNAME


ORDER BY P1.SALARY_YEARLY DESC

this are the results

this is the table for it:

INSERT INTO PRESENTERS(PRESENTERID, PNRLNAME, PNRFNAME, AGE,YEARS, SALARY_YEARLY)
VALUES

Answer

It maps each Presenter to each other Presenter and tells you how much different their salaries are. It uses a self-join to map presenters with each other.

The ON P2.PNRFNAME < P1.PNRFNAME join condition is perhaps unusual, but it works. That join ensures that you will not get symmetric comparisons such as Jason vs Tiff and Tiff vs Jason on separate rows. This is because given any two names, both of them are not equal to each other but only one of them is the first in alphanumeric order. If you used the clause NOT P2.PNRFNAME = P1.PNRFNAME, you would get the Jason vs Tiff and Tiff vs Jason doubling I mentioned.