I have two tables like this:
Table: tbl1
SELECT e1.* , tbl1.name
FROM evt_emp e1
LEFT JOIN evt_emp e2 ON e1.emp_id = e2.emp_id
AND e1.evt_emp_id < e2.evt_emp_id
LEFT JOIN tbl1 ON e1.emp_id = tbl1.empid
WHERE e2.evt_emp_id IS NULL
ORDER BY e1.evt_emp_id DESC;
Do you know any other way? Please suggest.
Here are complete MySQL statements to test / check:
CREATE TABLE IF NOT EXISTS `tbl1` (
`empid` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`city` varchar(255) NOT NULL,
PRIMARY KEY (`empid`)
);
INSERT INTO `tbl1` (`empid`, `name`, `city`) VALUES
(1, 'SVU', 'TPT'),
(2, 'SKU', 'ATP'),
(3, 'JNTU', 'HYD');
CREATE TABLE IF NOT EXISTS `evt_emp` (
`evt_emp_id` int(11) NOT NULL AUTO_INCREMENT,
`emp_id` int(11) NOT NULL,
`event_id` int(11) NOT NULL,
PRIMARY KEY (`evt_emp_id`)
);
INSERT INTO `evt_emp` (`evt_emp_id`, `emp_id`, `event_id`) VALUES
(1, 1, 100),
(2, 1, 121),
(3, 2, 20),
(4, 3, 21),
(5, 1, 150),
(6, 3, 200),
(7, 2, 256),
(8, 2, 258),
(9, 4, 500);
SELECT e1.* , tbl1.name
FROM evt_emp e1
LEFT JOIN evt_emp e2 ON e1.emp_id = e2.emp_id
AND e1.evt_emp_id < e2.evt_emp_id
LEFT JOIN tbl1 ON e1.emp_id = tbl1.empid
WHERE e2.evt_emp_id IS NULL
ORDER BY e1.evt_emp_id DESC;
Table: tbl1

Table: evt_emp
evt_emp table has tbl1.empid as emp_id and an event_id, when new event is created.
Now, we have to show latest event of each "emp" like this: Here is the MySQL query for this:
SELECT e1.* , tbl1.name
FROM evt_emp e1
LEFT JOIN evt_emp e2 ON e1.emp_id = e2.emp_id
AND e1.evt_emp_id < e2.evt_emp_id
LEFT JOIN tbl1 ON e1.emp_id = tbl1.empid
WHERE e2.evt_emp_id IS NULL
ORDER BY e1.evt_emp_id DESC;
Do you know any other way? Please suggest.
Here are complete MySQL statements to test / check:
CREATE TABLE IF NOT EXISTS `tbl1` (
`empid` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`city` varchar(255) NOT NULL,
PRIMARY KEY (`empid`)
);
INSERT INTO `tbl1` (`empid`, `name`, `city`) VALUES
(1, 'SVU', 'TPT'),
(2, 'SKU', 'ATP'),
(3, 'JNTU', 'HYD');
CREATE TABLE IF NOT EXISTS `evt_emp` (
`evt_emp_id` int(11) NOT NULL AUTO_INCREMENT,
`emp_id` int(11) NOT NULL,
`event_id` int(11) NOT NULL,
PRIMARY KEY (`evt_emp_id`)
);
INSERT INTO `evt_emp` (`evt_emp_id`, `emp_id`, `event_id`) VALUES
(1, 1, 100),
(2, 1, 121),
(3, 2, 20),
(4, 3, 21),
(5, 1, 150),
(6, 3, 200),
(7, 2, 256),
(8, 2, 258),
(9, 4, 500);
SELECT e1.* , tbl1.name
FROM evt_emp e1
LEFT JOIN evt_emp e2 ON e1.emp_id = e2.emp_id
AND e1.evt_emp_id < e2.evt_emp_id
LEFT JOIN tbl1 ON e1.emp_id = tbl1.empid
WHERE e2.evt_emp_id IS NULL
ORDER BY e1.evt_emp_id DESC;


No comments:
Post a Comment