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 !