Link to home
Start Free TrialLog in
Avatar of hdcowboyaz
hdcowboyazFlag for United States of America

asked on

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)
Avatar of lcohan
lcohan
Flag of Canada image

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;
Avatar of hdcowboyaz

ASKER

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
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;
I get no results when I run that.
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
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
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.
ASKER CERTIFIED SOLUTION
Avatar of Sharath S
Sharath S
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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'
>> 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?
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--,
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
;
What does the -9999 represent?  If I have more rows of data in the table does that need increased?
SELECT COUNT(EMP_ID) FROM emp;
9994

SELECT COUNT(EMPDATA_ID) FROM empdata;
10,967

SELECT COUNT(EMPHIRE_ID) FROM emphire;
27,101
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.
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.
I'm sorry. I wasn't paying attention that someone else answered.  I don't know how to go back and give Sharath credit?
Thanks Paul for all your comments and guidance to hdcowboyaz for closing the question in right way.
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