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

POPULAR ONLINE

list all the data, merge duplicate rows and sum the total using MySQL

ANTLR4 -no viable alternative at input 's4'

How to locate the Android ViewGroup element?

Issues displaying data from a PHP script on HTML 5 webpage

SHA1 conflict GCP and Firebase Auth

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

MySQL

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

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

MySQL

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