Get latest record from second table

145
February 01, 2018, at 8:12 PM

I am getting all employee list with first salary, I want all employee with latest salary credited.

Employee-Table

emp_id | emp_name
-------|--------- 
101    | Andrew
102    | John
103    | Alex

Salary-Table

sal_id | emp_id | month                 | salary
-------|--------|-----------------------|-------
201    | 101    | 1st jan 2018          | 10000
202    | 101    | 1st feb 2018          | 20000
203    | 101    | 1st march2018         | 20000
204    | 102    | 1st jan 2018          | 10000
205    | 102    | 1st feb 2018          | 20000
206    | 102    | 1st march2018         | 20000
207    | 103    | 1st jan 2018          | 10000
208    | 103    | 1st feb 2018          | 20000
209    | 103    | 1st march2018         | 20000

I want to fetch all employees list with latest month salary only. (all employee with march salary)

Thanks in advance.

Answer 1

This code should work :

<?php
    $date = date('F Y');
    $sql = "SELECT * FROM Employee-table et 
            INNER JOIN Salary-Table st ON et.emp_id = st.emp_id 
            WHERE st.month LIKE '%$date%'";
Answer 2
$date = date('F Y');
$saleries = $this->db->select('e.*, s.salary')
->join('Salary-Table s','s.emp_id = e.emp_id')
->like($date, 'match')
//order by the month and the group by emp id to give you the latest
->order_by('s.month', 'asc')
->group_by('e.emp_id')
//return results as an array of arrays
->get('Employee-table e')->result_array();

https://www.codeigniter.com/userguide3/database/query_builder.html

Rent Charter Buses Company
READ ALSO
When creating user roles, should I create 3 tables, users-users_roles-roles or should I just create a role column in the users table?

When creating user roles, should I create 3 tables, users-users_roles-roles or should I just create a role column in the users table?

In my previous project, I had a users table that had a role column which had an integer insideBy default, the integer that would be written inside the column when a user registers was 2, which meant he was a normal user

157
Parsing php count as json in android app

Parsing php count as json in android app

My php script below allows me to get posts as well as corresponding user details from their respective mysql tables yet when I try to get the likes count from the likes table my app is unable to parse the dataAny ideas please

202
MySQL Trigger IF Statement OR not workign when two conditions match

MySQL Trigger IF Statement OR not workign when two conditions match

I'm fairly new to MySQL Trigger sintax and I'm having a problem that I could not find a clear answer anywhere

165
Update with only specify data information

Update with only specify data information

Im using multiple form to update specific data and i want know if it is correct way can be done in one form to perform all action?

162