MySQL Forums
Forum List  »  General

Order by not working with join
Posted by: Robin vanpersie
Date: October 09, 2020 02:38AM

In a company there are employees and each employee may borrow from his salary many times.
It 's required to know the sum of money amount that employee borrowed it and the remaining salary after each borrowing transaction,and the last money amount that specified employee he/she borrowed.

So I have table employees as following

CREATE TABLE `Employees`
(
  `Employee_Id` Int NOT NULL AUTO_INCREMENT,
  `Employee_Name` Char(150) NOT NULL,
  `Job` Varchar(100),
  `Salary` Decimal(10,2),
  PRIMARY KEY (`Employee_Id`)
)
;

And table for borrowing transaction

-- Table Salary_Transaction

CREATE TABLE `Salary_Transaction`
(
  `Salary_Transaction_Id` Int NOT NULL AUTO_INCREMENT,
  `money_amount` Decimal(10,2) NOT NULL,
  `Employee_Id` Int,
  PRIMARY KEY (`Salary_Transaction_Id`)
)
;
Indexes and Relathionships

CREATE INDEX `IX_Relationship11` ON `Salary_Transaction` (`Employee_Id`)
;
ALTER TABLE `Salary_Transaction` ADD CONSTRAINT `Relationship11` FOREIGN KEY (`Employee_Id`) REFERENCES `Employees` (`Employee_Id`) ON DELETE CASCADE ON UPDATE CASCADE
;

This my code but It doesn't show last money amount that specified employee he/she borrows it shows the first

SELECT e.Employee_Id,e.Employee_Name,sum(money_amount), e.Salary-sum(money_amount),money_amount from employees e INNER join salary_transaction S where s.Employee_Id = e.Employee_Id and e.Employee_Id = 12 ORDER BY Employee_Id DESC limit 1

If I use order by without join it works perfect !

Options: ReplyQuote


Subject
Written By
Posted
Order by not working with join
October 09, 2020 02:38AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.