Re: PostgreSQL Advocacy, Thoughts and Comments

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.)
--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight, UK                             http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "Who shall ascend into the hill of the LORD? or who
      shall stand in his holy place? He that hath clean
      hands, and a pure heart..."            Psalms 24:3,4