Re: PostgreSQL Advocacy, Thoughts and Comments

On Sat, 2003-11-29 at 04:37, cnliou wrote:
> "Jason Tesser" <JTesser(at)nbbc(dot)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(dot)Elphick(at)lfix(dot)co(dot)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