SubQuery

SELECT e.EMP_ID, h.ACTIONDATE, h.ACTION
FROM emp e, empdata d, emphire h
WHERE e.EMP_ID = d.EMP_ID AND d.EMPDATA_ID = h.EMPDATA_ID
AND e.DELETED = 'N' AND d.DELETED = 'N' AND h.DELETED = 'N'
AND d.EPAY NOT LIKE 'SAL%' AND d.EPAY NOT LIKE 'REGADM'
AND d.ECOMPANY = 'A' AND d.EDEPT = 'H'
ORDER BY d.EMPDATA_ID, h.ACTIONDATE;

In the above query I need to select the only rows if d.ACTION = 'TERM' for the MAX(h.ACTIONDATE) when Group By (e.EMP_ID)
hdcowboyazAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lcohanDatabase AnalystCommented:
SELECT MP_ID, ACTIONDATE, ACTION
FROM
(
SELECT e.EMP_ID, MAX(h.ACTIONDATE) as ACTIONDATE, h.ACTION
      FROM emp e, empdata d, emphire h
      WHERE e.EMP_ID = d.EMP_ID AND d.EMPDATA_ID = h.EMPDATA_ID
            AND e.DELETED = 'N' AND d.DELETED = 'N' AND h.DELETED = 'N'
            AND d.EPAY NOT LIKE 'SAL%' AND d.EPAY NOT LIKE 'REGADM'
            AND d.ECOMPANY = 'A' AND d.EDEPT = 'H'
AND d.ACTION = 'TERM'
GROUP BY e.EMP_ID
) q
ORDER BY d.EMPDATA_ID, h.ACTIONDATE;
0
hdcowboyazAuthor Commented:
This is the data. The querry should not select EMP ID's: 19, 57 & 130 becasuse the ACTION for their MAX dates IS NOT 'TERM'

EMP ID      DATE                       ACTION
13      9/5/1995                       HIRE
13      1/30/2009      TERM
13      4/11/2011      REHIRE
13      5/20/2011      TERM
16      6/3/1996                        HIRE
16      2/17/2007      TERM
19      5/15/1997      HIRE
20      5/27/1997      HIRE
20      11/16/2005      REHIRE
20      2/18/2009      TERM
20      10/24/2011      REHIRE
20      10/25/2011      TERM
22      5/7/1998                        HIRE
22      11/20/2007      TERM
57      6/4/2001                        HIRE
57      3/28/2008      TERM
57      6/11/2008      REHIRE
64      11/5/2001      HIRE
64      3/3/2008                       REHIRE
64      7/22/2009      TERM
67      12/10/2001      HIRE
67      3/10/2009      REHIRE
67      4/28/2009      TERM
121      9/11/2002      HIRE
121      3/10/2005      TERM
122      9/11/2002      HIRE
122      7/23/2004      TERM
130      10/19/2002      HIRE
130      10/1/2008      TERM
130      10/1/2008      REHIRE
130      2/20/2012      REHIRE

This is the query... When I run it it does filter out EMP ID 19 but NOT 57 & 130

SELECT EMP_ID, ACTIONDATE, `ACTION`
FROM
(
SELECT e.EMP_ID, MAX(h.ACTIONDATE) AS ACTIONDATE, h.ACTION
      FROM emp e, empdata d, emphire h
      WHERE e.EMP_ID = d.EMP_ID AND d.EMPDATA_ID = h.EMPDATA_ID
            AND e.DELETED = 'N' AND d.DELETED = 'N' AND h.DELETED = 'N'
            AND d.EPAY NOT LIKE 'SAL%' AND d.EPAY NOT LIKE 'REGADM'
            AND d.ECOMPANY = 'A' AND d.EDEPT = 'H'
            AND h.ACTION = 'TERM'
GROUP BY e.EMP_ID
ORDER BY d.EMPDATA_ID, h.ACTIONDATE
) q;

EMP_ID      ACTIONDATE      ACTION
13      5/20/2011      TERM
16      2/17/2007      TERM
20      10/25/2011      TERM
22      11/20/2007      TERM
57      3/28/2008      TERM
64      7/22/2009      TERM
67      4/28/2009      TERM
121      3/10/2005      TERM
122      7/23/2004      TERM
130      10/1/2008      TERM
0
lcohanDatabase AnalystCommented:
SELECT EMP_ID, ACTIONDATE, ACTION
FROM
(
SELECT e.EMP_ID, MAX(h.ACTIONDATE) as ACTIONDATE, h.ACTION
      FROM emp e, empdata d, emphire h
      WHERE e.EMP_ID = d.EMP_ID AND d.EMPDATA_ID = h.EMPDATA_ID
            AND e.DELETED = 'N' AND d.DELETED = 'N' AND h.DELETED = 'N'
            AND d.EPAY NOT LIKE 'SAL%' AND d.EPAY NOT LIKE 'REGADM'
            AND d.ECOMPANY = 'A' AND d.EDEPT = 'H'
GROUP BY e.EMP_ID
) q
WHERE ACTION = 'TERM'
ORDER BY EMP_ID, ACTIONDATE;
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

hdcowboyazAuthor Commented:
I get no results when I run that.
0
SharathData EngineerCommented:
try this query.
SELECT e.EMP_ID, 
       h.ACTIONDATE, 
       h.ACTION 
  FROM emp e, 
       empdata d, 
       emphire h 
 WHERE e.EMP_ID = d.EMP_ID 
   AND d.EMPDATA_ID = h.EMPDATA_ID 
   AND e.DELETED = 'N' 
   AND d.DELETED = 'N' 
   AND h.DELETED = 'N' 
   AND d.EPAY NOT LIKE 'SAL%' 
   AND d.EPAY NOT LIKE 'REGADM' 
   AND d.ECOMPANY = 'A' 
   AND d.EDEPT = 'H' 
   AND e.emp_id IN (SELECT emp_id 
                      FROM (SELECT t1.*, 
                                   IF(@emp_id = emp_id, @rownum := @rownum + 1, @rownum := 1) AS RowNum,
                                   IF(@emp_id <> emp_id, @emp_id := emp_id, -9999)            AS dummy
                              FROM (SELECT e.EMP_ID, 
                                           h.ACTIONDATE, 
                                           h.ACTION 
                                      FROM emp e, 
                                           empdata d, 
                                           emphire h 
                                     WHERE e.EMP_ID = d.EMP_ID 
                                       AND d.EMPDATA_ID = h.EMPDATA_ID 
                                       AND e.DELETED = 'N' 
                                       AND d.DELETED = 'N' 
                                       AND h.DELETED = 'N' 
                                       AND d.EPAY NOT LIKE 'SAL%' 
                                       AND d.EPAY NOT LIKE 'REGADM' 
                                       AND d.ECOMPANY = 'A' 
                                       AND d.EDEPT = 'H') t1, 
                                   (SELECT @rownum := 1, 
                                           @emp_id := -9999) r 
                             ORDER BY emp_id, 
                                      actiondate DESC) t1 
                     WHERE rownum = 1 
                       AND action = 'TERM') 
 ORDER BY e.emp_id, 
          d.ACTIONDATE; 

Open in new window

Tested with your sample here: http://sqlfiddle.com/#!8/93bda/4
0
hdcowboyazAuthor Commented:
Query : SELECT e.EMP_ID,         h.ACTIONDATE,         h.ACTION    FROM emp e,         empdata d,         emphire h   WHERE e.EMP_ID = d...
Error Code : 1054
Unknown column 'd.ACTIONDATE' in 'order clause'


Query : 13,16,20,22  19,57 & 130
Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '13,16,20,22
19,57 & 130' at line 1
0
hdcowboyazAuthor Commented:
When I said this is the data, I meant it's the result of the query from the three tables (emp, empdata & emphire)

Query
SELECT e.EMP_ID, h.ACTIONDATE, h.ACTION
FROM emp e, empdata d, emphire h
WHERE e.EMP_ID = d.EMP_ID AND d.EMPDATA_ID = h.EMPDATA_ID
AND e.DELETED = 'N' AND d.DELETED = 'N' AND h.DELETED = 'N'
AND d.EPAY NOT LIKE 'SAL%' AND d.EPAY NOT LIKE 'REGADM'
AND d.ECOMPANY = 'A' AND d.EDEPT = 'H'
ORDER BY d.EMPDATA_ID, h.ACTIONDATE;

Results
EMP ID      DATE      ACTION
13      9/5/1995      HIRE
13      1/30/2009      TERM
13      4/11/2011      REHIRE
13      5/20/2011      TERM
16      6/3/1996      HIRE
16      2/17/2007      TERM
19      5/15/1997      HIRE
20      5/27/1997      HIRE
20      11/16/2005      REHIRE
20      2/18/2009      TERM
20      10/24/2011      REHIRE
20      10/25/2011      TERM
22      5/7/1998      HIRE
22      11/20/2007      TERM
57      6/4/2001      HIRE
57      3/28/2008      TERM
57      6/11/2008      REHIRE
64      11/5/2001      HIRE
64      3/3/2008      REHIRE
64      7/22/2009      TERM
67      12/10/2001      HIRE
67      3/10/2009      REHIRE
67      4/28/2009      TERM
121      9/11/2002      HIRE
121      3/10/2005      TERM
122      9/11/2002      HIRE
122      7/23/2004      TERM
130      10/19/2002      HIRE
130      10/1/2008      TERM
130      10/1/2008      REHIRE
130      2/20/2012      REHIRE

What is needed, I beleive is a subquery to only show results where d.ACTION = 'TERM' for the MAX(h.ACTIONDATE) when Group By (e.EMP_ID)

This should be the final results
EMP ID      DATE      ACTION
13      5/20/2011      TERM
16      2/17/2007      TERM
20      10/25/2011      TERM
22      11/20/2007      TERM
64      7/22/2009      TERM
67      4/28/2009      TERM
121      3/10/2005      TERM
122      7/23/2004      TERM

19, 57 & 130 should be filtered out.
0
SharathData EngineerCommented:
try this.
SELECT t1.EMP_ID,t1.ACTIONDATE,t1.ACTION 
  FROM (SELECT t2.*, 
               IF(@emp_id = EMP_ID, @rownum := @rownum + 1, @rownum := 1) AS RowNum, 
               IF(@emp_id <> emp_id, @emp_id := EMP_ID, -9999)            AS dummy 
          FROM (SELECT e.EMP_ID, 
                       h.ACTIONDATE, 
                       h.ACTION 
                  FROM emp e, 
                       empdata d, 
                       emphire h 
                 WHERE e.EMP_ID = d.EMP_ID 
                   AND d.EMPDATA_ID = h.EMPDATA_ID 
                   AND e.DELETED = 'N' 
                   AND d.DELETED = 'N' 
                   AND h.DELETED = 'N' 
                   AND d.EPAY NOT LIKE 'SAL%' 
                   AND d.EPAY NOT LIKE 'REGADM' 
                   AND d.ECOMPANY = 'A' 
                   AND d.EDEPT = 'H') t2, 
               (SELECT @rownum := 1, 
                       @emp_id := -9999) r 
         ORDER BY EMP_ID, 
                  ACTIONDATE DESC) t1 
 WHERE rownum = 1 
   AND ACTION = 'TERM' 
 ORDER BY EMP_ID, 
          ACTIONDATE;

Open in new window



http://sqlfiddle.com/#!8/93bda/6
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
hdcowboyazAuthor Commented:
That worked, however I left off several columns initially so as to make the query more easily explained.  I've added these extra colums now and they work.

SELECT t1.EMP_ID,t1.ACTIONDATE,t1.ACTION,t1.ERATE,t1.ECCLASS,t1.EMISC1,t1.PTELEPH,t1.EPHONE2,t1.EEMAIL
  FROM (SELECT t2.*,
               IF(@emp_id = EMP_ID, @rownum := @rownum + 1, @rownum := 1) AS RowNum,
               IF(@emp_id <> emp_id, @emp_id := EMP_ID, -9999)            AS dummy
          FROM (SELECT e.EMP_ID,
                       h.ACTIONDATE,
                       h.ACTION,
                       d.ERATE,
                       d.ECCLASS,
                       d.EMISC1,
                       d.PTELEPH,
                       d.EPHONE2,
                       d.EEMAIL
                  FROM emp e,
                       empdata d,
                       emphire h
                 WHERE e.EMP_ID = d.EMP_ID
                   AND d.EMPDATA_ID = h.EMPDATA_ID
                   AND e.DELETED = 'N'
                   AND d.DELETED = 'N'
                   AND h.DELETED = 'N'
                   AND d.EPAY NOT LIKE 'SAL%'
                   AND d.EPAY NOT LIKE 'REGADM'
                   AND d.ECOMPANY = 'A'
                   AND d.EDEPT = 'H') t2,
               (SELECT @rownum := 1,
                       @emp_id := -9999) r
         ORDER BY EMP_ID,
                  ACTIONDATE DESC) t1
 WHERE rownum = 1
   AND ACTION = 'TERM'
 ORDER BY EMP_ID,
          ACTIONDATE;

However, I want to add two more colums but I can't get them to work.

ROUND(DATEDIFF('2013-10-04', e.EBORN)/365.25,0)
CONCAT(d.ECOMPANY, d.EDEPT)

ALso, how do you add an alias (e.g.,  d.EMISC1 'POSITION'
0
PortletPaulfreelancerCommented:
>> I want to add two more columns ...
ROUND(DATEDIFF('2013-10-04', e.EBORN)/365.25,0) ***see at end
CONCAT(d.ECOMPANY, d.EDEPT)
Lines 7 & 8 and lines 24 & 25 below will introduce those 2 columns

nb: CURDATE() might be used instead of '2013-10-04',
but wasn't certain if this is what you intended

Lines 9 & 27 implement the alias wanted for d.EMISC1  as POSITION
SELECT
       t1.EMP_ID
     , t1.ACTIONDATE
     , t1.ACTION
     , t1.ERATE
     , t1.ECCLASS
     , t1.AppproxAge
     , t1.CompanyDept
     , t1.POSITION
     , t1.PTELEPH
     , t1.EPHONE2
     , t1.EEMAIL
FROM (
         SELECT
                t2.* 
              , IF(@emp_id = EMP_ID, @rownum := @rownum + 1, @rownum := 1) AS RowNum--, 
              , IF(@emp_id <> emp_id, @emp_id := EMP_ID, -9999)            AS dummy 
         FROM (
                  SELECT
                         e.EMP_ID
                       , h.ACTIONDATE
                       , h.ACTION
                       , d.ERATE
                       , d.ECCLASS
                       , ROUND(DATEDIFF('2013-10-04', e.EBORN)/365.25,0) as AppproxAge
                       , CONCAT(d.ECOMPANY, d.EDEPT) as CompanyDept
                       , d.EMISC1 as POSITION
                       , d.PTELEPH
                       , d.EPHONE2
                       , d.EEMAIL
                  FROM emp e
                       , empdata d
                       , emphire h
                  WHERE e.EMP_ID = d.EMP_ID
                  AND d.EMPDATA_ID = h.EMPDATA_ID
                  AND e.DELETED = 'N'
                  AND d.DELETED = 'N'
                  AND h.DELETED = 'N'
                  AND d.EPAY NOT LIKE 'SAL%'
                  AND d.EPAY NOT LIKE 'REGADM'
                  AND d.ECOMPANY = 'A'
                  AND d.EDEPT = 'H'
              ) t2
              , (SELECT @rownum := 1,@emp_id := -9999) r 
         ORDER BY
                EMP_ID
              , ACTIONDATE DESC
     ) t1
WHERE rownum = 1
AND ACTION = 'TERM'
ORDER BY
       EMP_ID
     , ACTIONDATE
;

Open in new window

*** ROUND(DATEDIFF('2013-10-04', e.EBORN)/365.25,0)
this is not an accurate calculation of age and is prone to error

Do you need a more accurate method?
0
hdcowboyazAuthor Commented:
Query : SELECT        t1.EMP_ID      , t1.ACTIONDATE      , t1.ACTION      , t1.ERATE      , t1.ECCLASS      , t1.AppproxAge      , t1.C...
Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '--,
              , IF(@emp_id <> emp_id, @emp_id := EMP_ID, -9999)            ' at line 16

No the age appoximation is okay.

Did you want the " - -" after RowNum. It still fails whether I leave it in or take it out.

IF(@emp_id = EMP_ID, @rownum := @rownum + 1, @rownum := 1) AS RowNum--,
0
hdcowboyazAuthor Commented:
WORKS. There was an extra comma

SELECT
       t1.EMP_ID
     , t1.ACTIONDATE
     , t1.ACTION
     , t1.ERATE
     , t1.ECCLASS
     , t1.AppproxAge
     , t1.CompanyDept
     , t1.POSITION
     , t1.PTELEPH
     , t1.EPHONE2
     , t1.EEMAIL
FROM (
         SELECT
                t2.*
               , IF(@emp_id = EMP_ID, @rownum := @rownum + 1, @rownum := 1) AS RowNum
               , IF(@emp_id <> emp_id, @emp_id := EMP_ID, -9999)            AS dummy
         FROM (
                  SELECT
                         e.EMP_ID
                       , h.ACTIONDATE
                       , h.ACTION
                       , d.ERATE
                       , d.ECCLASS
                       , ROUND(DATEDIFF('2013-10-04', e.EBORN)/365.25,0) AS AppproxAge
                       , d.EMISC1 AS POSITION
                       , d.PTELEPH
                       , d.EPHONE2
                       , d.EEMAIL
                       , CONCAT(d.ECOMPANY, d.EDEPT) AS CompanyDept
                  FROM emp e
                       , empdata d
                       , emphire h
                  WHERE e.EMP_ID = d.EMP_ID
                  AND d.EMPDATA_ID = h.EMPDATA_ID
                  AND e.DELETED = 'N'
                  AND d.DELETED = 'N'
                  AND h.DELETED = 'N'
                  AND d.EPAY NOT LIKE 'SAL%'
                  AND d.EPAY NOT LIKE 'REGADM'
                  AND d.ECOMPANY = 'A'
                  AND d.EDEPT = 'H'
              ) t2
              , (SELECT @rownum := 1,@emp_id := -9999) r
         ORDER BY
                EMP_ID
              , ACTIONDATE DESC
     ) t1
WHERE rownum = 1
AND ACTION = 'TERM'
ORDER BY
       EMP_ID
     , ACTIONDATE
;
0
hdcowboyazAuthor Commented:
What does the -9999 represent?  If I have more rows of data in the table does that need increased?
0
hdcowboyazAuthor Commented:
SELECT COUNT(EMP_ID) FROM emp;
9994

SELECT COUNT(EMPDATA_ID) FROM empdata;
10,967

SELECT COUNT(EMPHIRE_ID) FROM emphire;
27,101
0
PortletPaulfreelancerCommented:
the -9999 is not related to the number of records in any table.

the method introduced by Sharath (who did most of the work here) emulates "row_number()" in MySQL and the purpose of it is to force a "partitioning" of the roe numbers per emp_id.

Unless you have emp_id values that are negative, which is very unlikely, then you should not need to worry about the -9999

Note, all I did was introduce a few extra fields, as you expanded the question.

>>"That worked, "
in reference to Sharath's work - so at that point the question was answered.

Everthing added after "That worked" should have been a new question I believe, and Sharath would feel justified in asking for points to be given to him.


>>"however I left off several columns initially"
This is not a comment directed solely at you - but a general one.
It has been my experience that when questions are "simplified" they tend to get long and slow to answer - often frustrating all parties.
It is preferable to state the full question up front and without simplification.
0
PortletPaulfreelancerCommented:
btw: sorry about the missing or extra comma - not sure where it was or how I missed it - but when it's not possible to run a query syntax errors can go undetected.
0
hdcowboyazAuthor Commented:
I'm sorry. I wasn't paying attention that someone else answered.  I don't know how to go back and give Sharath credit?
0
SharathData EngineerCommented:
Thanks Paul for all your comments and guidance to hdcowboyaz for closing the question in right way.
0
PortletPaulfreelancerCommented:
Least I could do Sharath - it wasn't my intention to leverage your work to my advantage - just wanted to see that the question(s) solved for hdcowboyaz
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.