Re: PostgreSQL Advocacy, Thoughts and Comments

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