Hi,
I need to explain this abit....
I am using MySQL awith C# to develop Windows Form application..
I have the following tables:
CREATE TABLE `salary_slip_category` (
`salary_slip_category_id` int(11) NOT NULL AUTO_INCREMENT,
`salary_slip_category_name` varchar(255) DEFAULT NULL,
`salary_slip_category_plus_or_minus` varchar(1) DEFAULT NULL,
`salary_slip_category_default_description` varchar(255) DEFAULT NULL,
`salary_slip_category_order` int(11) DEFAULT NULL,
PRIMARY KEY (`salary_slip_category_id`),
KEY `idx_salary_slip_category_salary_slip_category_name` (`salary_slip_category_name`),
KEY `idx_salary_slip_category_salary_slip_category_plus_or_minus` (`salary_slip_category_plus_or_minus`),
KEY `idx_salary_slip_category_salary_slip_category_default_descriptio` (`salary_slip_category_default_description`),
KEY `idx_salary_slip_category_salary_slip_category_order` (`salary_slip_category_order`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;
CREATE TABLE `salary_slip_details` (
`salary_slip_details_id` int(11) NOT NULL AUTO_INCREMENT,
`employee_number` int(11) DEFAULT NULL,
`is_benefit` bit(1) DEFAULT b'0',
`salary_slip_details_order` int(11) DEFAULT '999',
`trxn_code` varchar(50) DEFAULT NULL,
`salary_slip_id` int(11) DEFAULT NULL,
`salary_slip_code` int(11) DEFAULT NULL,
`salary_slip_month` int(11) DEFAULT NULL,
`salary_slip_year` int(11) DEFAULT NULL,
`salary_slip_details_category` varchar(255) DEFAULT NULL,
`salary_slip_category_plus_or_minus` varchar(1) DEFAULT NULL,
`salary_slip_details_description` varchar(255) DEFAULT NULL,
`salary_slip_details_amount` double(10,3) DEFAULT NULL,
`salary_slip_details_released` bit(1) DEFAULT b'0',
PRIMARY KEY (`salary_slip_details_id`),
KEY `idx_salary_slip_details_employee_number` (`employee_number`),
KEY `idx_salary_slip_details_is_benefit` (`is_benefit`),
KEY `idx_salary_slip_details_salary_slip_details_order` (`salary_slip_details_order`),
KEY `idx_salary_slip_details_trxn_code` (`trxn_code`),
KEY `idx_salary_slip_details_salary_slip_id` (`salary_slip_id`),
KEY `idx_salary_slip_details_salary_slip_code` (`salary_slip_code`),
KEY `idx_salary_slip_details_salary_slip_month` (`salary_slip_month`),
KEY `idx_salary_slip_details_salary_slip_year` (`salary_slip_year`),
KEY `idx_salary_slip_details_salary_slip_details_category` (`salary_slip_details_category`),
KEY `idx_salary_slip_details_salary_slip_category_plus_or_minus` (`salary_slip_category_plus_or_minus`),
KEY `idx_salary_slip_details_salary_slip_details_description` (`salary_slip_details_description`),
KEY `idx_salary_slip_details_salary_slip_details_amount` (`salary_slip_details_amount`),
KEY `idx_salary_slip_details_salary_slip_details_released` (`salary_slip_details_released`)
) ENGINE=InnoDB AUTO_INCREMENT=171 DEFAULT CHARSET=utf8;
CREATE TABLE `employee` (
`employee_number` int(11) NOT NULL AUTO_INCREMENT,
`id_number` varchar(255) DEFAULT NULL,
`employee_name` varchar(255) DEFAULT NULL,
`employee_gender` varchar(11) DEFAULT NULL,
`employee_designation` varchar(255) DEFAULT NULL,
`date_of_birth` date DEFAULT NULL,
`employee_email` varchar(255) DEFAULT NULL,
`employee_mobile` varchar(255) DEFAULT NULL,
`employee_home_tel` varchar(255) DEFAULT NULL,
`date_of_join` date DEFAULT NULL,
`last_working_date` date DEFAULT NULL,
`bank_name` varchar(255) DEFAULT NULL,
`bank_account` varchar(50) DEFAULT NULL,
`current_employee` bit(1) DEFAULT b'1',
`is_active` bit(1) DEFAULT b'1',
`login_password` varchar(255) DEFAULT NULL,
`login_pin` varchar(4) DEFAULT NULL,
`created_date` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`employee_number`),
KEY `idx_employee_id_number` (`id_number`),
KEY `idx_employee_employee_name` (`employee_name`),
KEY `idx_employee_employee_gender` (`employee_gender`),
KEY `idx_employee_employee_designation` (`employee_designation`),
KEY `idx_employee_date_of_birth` (`date_of_birth`),
KEY `idx_employee_employee_email` (`employee_email`),
KEY `idx_employee_employee_mobile` (`employee_mobile`),
KEY `idx_employee_employee_home_tel` (`employee_home_tel`),
KEY `idx_employee_date_of_join` (`date_of_join`),
KEY `idx_employee_last_working_date` (`last_working_date`),
KEY `idx_employee_bank_name` (`bank_name`),
KEY `idx_employee_bank_account` (`bank_account`),
KEY `idx_employee_current_employee` (`current_employee`),
KEY `idx_employee_s_active` (`is_active`),
KEY `idx_employee_login_password` (`login_password`),
KEY `idx_employee_login_pin` (`login_pin`)
) ENGINE=InnoDB AUTO_INCREMENT=55 DEFAULT CHARSET=utf8;
I am using the salary_slip_details to view the salary details, linking it with employee_number in the employee table and also linking it to the salary_slip_category to view it by category
I want to know how can I make the result showing like this:
1. every employee will have one row.
2. Categories will be shown as columns and total amount of the category will be shown there
so if the employee has 2 record of decution (category) 10 dollar each the in the deduction column it will show 20$ for that employee
kindly help....
|