Hi,
I am facing some problems while writing a MySQL query for a complex
scenario.
I have a table with employments for all executives and a flag to
distinguish the current and previous employments(titles in different
companies or previous titles in the same company).
Here are 2 examples:
i. Employment history of Emp X.
Current Employments:
Company A - Director (Jan 1, 1996 - Present)
Company B
</iv/companyinfo.do?methodToCall=overview&id=40288084049fb47401049fb483270021>
- Chairman (Jan 1, 2000 - Present)
Previous Employments:
Company C
</iv/companyinfo.do?methodToCall=overview&id=40288084049fb47401049fb482aa000f>
- Director (Apr 1, 1998 - Dec 24, 2004)
Company C
</iv/companyinfo.do?methodToCall=overview&id=40288084049fb47401049fb482aa000f>
- Lead Director (Apr 1, 1998 - Feb 18, 2004)
i. Employment history of Emp Y.
Current Employments:
No current employments
Previous Employments:
Company D
</iv/companyinfo.do?methodToCall=overview&id=40288084049fb47401049fb482aa000f>
- President (Apr 1, 1998 - Dec 24, 2005)
Company
</iv/companyinfo.do?methodToCall=overview&id=40288084049fb47401049fb482aa000f>D
- Vice President (Apr 1, 1998 - Feb 18, 2004)
The search interface allows to search on the first name and /or last
name of the executives and search results need follow the below rules:
1. Show current employment record only if one or more exist for that
person(multiple records if there are multiple current employments for a
person, else single record).
2. Show previous employment if there is no current employment and this
needs to be the most recent employment record. For this the current
employment column in the results needs to be empty.
The results should look like this:
Name Title Current Company
Emp X Director Company A
Emp X Chairman Company B
Emp Y President -
To implement rule 2, I tried using 'group by' clause on executive_id,
but if I do that, then I get only one record as it groups both the
active and inactive records for each executive. My requirement is to get
the most recent employment record only for those executives who don't
have a current employment(active) record and in other case get multiple
current employment records. Is there anyway this kind of conditional
queries can be written using MySQL?
Thank you,
Harini
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
