On Sat, 29 Nov 2003, Oliver Elphick wrote:
> On Sat, 2003-11-29 at 04:37, cnliou wrote:
> > "Jason Tesser" <JTesser@nbbc.edu>
> >
> > > MySQL cannot even handle sub-queries yet.
> >
> > Ohh! Really?
> > Allow me to pay my highest respect to the genius mySQL
> > programmers!
> > I completely have no clue on how to construct any single
> > tiny database on a DBMS having no sub-query capability.
> >
> > Being too dumb, I solicit mySQL programmers' help by showing
> > me employee FOO's birthday and his/her latest job title
> > effective on or before 2003-1-1 from the following tables:
> >
> > CREATE TABLE t1 (employee TEXT,BirthDay DATE);
> > CREATE TABLE t2 (employee TEXT,EffectiveDate DATE,JobTitle
> > TEXT);
> >
> > And make the result like this:
> >
> > FOO 1980-1-1 programmer
> >
> > Please do not give me the answer that you will merge these
> > two tables to form one like this:
> >
> > CREATE TABLE t1 (employee TEXT,BirthDay DATE,EffectiveDate
> > DATE,JobTitle TEXT);
>
> I have great trouble following your meaning, but I think you are talking
> about joining two tables in a query:
>
> SELECT t1.employee, t1.birthday, t2.jobtitle
> FROM t1, t2
> WHERE t1.employee = t2.employee;
>
> That is not the same as using a sub-query:
>
> SELECT employee
> FROM t1
> WHERE birthday > (
> SELECT MIN(effectivedate)
> FROM t2
> );
>
> (select employees who were born after the longest-serving employee
> started work.)
>
I think he means for the employee FOO show only the latest job title. Or in
other words:
SELECT t1.employee, t1.birthday, t2.jobtitle
FROM t1, t2
WHERE
t1.employee = t2.employee
AND
t1.employee = 'FOO'
AND
t2.effectivedate > CAST('2003-1-1' TO DATE)
ORDER BY t2.effectivedate DESC
LIMIT 1
which of course uses a PostgreSQL customisation.
I've got a feeling it's possible doing self joins and the like but I'll leave
it at that I think.
--
Nigel
