I'm not sure how to join two tables onto my main query. The two tables that determine the accountnumber join to each of the 3 main tables for different situations.
I'm trying to determine the accountnumber based on three things. An accountnumber is based on a paycode, department and employee type which are three different tables.
Here is how they should be joined.
/* all accountnumbers have a paycode_id*/
accountcodes.paycode_id = employee_pay.id
/* all accountnumbers are assigned to either a certain department or all*/
accountcodes.department_code = department.code
/* the accountnumber can be assigned to one emp_type or both*/
accountcodes.emp_type_id = employee_infos.emp_type_id
/* the accountnumber is in table:lkp_accountcodes, that determines the account number table:accountcodes */
accountcodes.lkp_accountcodes_id = lkp_accountcodes.id
table:accountcodes
-----------------------------------------------------------------------
| ID | lkp_accountcodes_id | paycode_id | department_code | emp_type_id |
|--------------------------|------------|-----------------|-------------|
| 1 | 21 | 15 | 120 | 1 |
| 2 | 22 | 15 | 310 | 1 |
| 3 | 23 | 30 | null | 1 |
| 4 | 24 | 30 | null | 2 |
| 5 | 25 | 55 | 120 | 1 |
| 6 | 26 | 55 | 310 | 2 |
| 7 | 27 | 55 | 120 | 2 |
-----------------------------------------------------------------------
table:lkp_accountcodes
-----------------------------------
| id | company_id | accountnumber |
|-----|------------|----------------|
| 21 | 500 | 5210 |
| 22 | 500 | 6210 |
| 23 | 500 | 2211 |
| 24 | 500 | 2210 |
| 25 | 500 | 5010 |
| 26 | 500 | 6000 |
| 27 | 500 | 5090 |
-----------------------------------
I don't know if I should be doing three left joins, create temporary tables, or one big left outer join like the one below? Also, I'm not sure how to group it and if the departent code is null then the accountnumber should be detemined by the paycode_id and emp_type_id. Help me with the Query below.
SELECT i.employee, d.department, e1.paycode, a1.accountnumber
FROM employee_pay e1
INNER JOIN employee_infos i ON e1.emp_info_id = i.id
INNER JOIN department d ON i.department_id = d.id
LEFT OUTER JOIN accountcodes ac ON ac.paycode_id = e1.id
AND ac.emp_type_id = i.emp_type_id
AND ac.department_code = d.code -- if null?
LEFT OUTER JOIN lkp_accountcodes lgc on gp.lkp_gl_code_id = lgc.id
-- group?
expected result
emp_number | emp_type | deptartment | pay_code | account_number
123 | temp | 120 | CPP Ded | 5210
456 | reg | 310 | CPP Ded | 6210
789 | temp | null | ExpReim | 2210
987 | reg | null | ExpReim | 2211
654 | reg | 145 | StatHol | 5010
321 | temp | 145 | StatHol | 5090
333 | temp | 532 | StatHol | 6000
Do the fact you have not aggregation function then if you want avoid duplicated row instead of GROUP BY you should use DISTINCT
SELECT DISTINCT i.employee, d.department, e1.paycode, a1.accountnumber
FROM employee_pay e1
INNER JOIN employee_infos i ON e1.emp_info_id = i.id
INNER JOIN department d ON i.department_id = d.id
LEFT JOIN accountcodes ac ON ac.paycode_id = e1.id
AND ac.emp_type_id = i.emp_type_id
AND ac.department_code = d.code
LEFT JOIN lkp_accountcodes lgc on gp.lkp_gl_code_id = lgc.id