Re: self join

Em qua, 10 de abr de 2019 às 09:23, PG Doc comments form
<[email protected]> escreveu:
>
> The example of self join shows two resulting records.
> I have checked the input data.
> There are three output records with lower lo and higher high temperatures
>
> We seem to be missing, in the answer, a third record:
> ( (San Francisco, 46, 50) , (Hayward, 54, 37 ))
>
No, it is not. See src/tutorial/basics.source. I reproduce some
commands of that file above. Note that that self join returns only 2
records.

CREATE TABLE weather (
city varchar(80),
temp_lo int, -- low temperature
temp_hi int, -- high temperature
prcp real, -- precipitation
date date
);

CREATE TABLE cities (
name varchar(80),
location point
);

INSERT INTO weather
    VALUES ('San Francisco', 46, 50, 0.25, '1994-11-27');

INSERT INTO cities
    VALUES ('San Francisco', '(-194.0, 53.0)');

INSERT INTO weather (city, temp_lo, temp_hi, prcp, date)
    VALUES ('San Francisco', 43, 57, 0.0, '1994-11-29');

INSERT INTO weather (date, city, temp_hi, temp_lo)
    VALUES ('1994-11-29', 'Hayward', 54, 37);

SELECT W1.city, W1.temp_lo, W1.temp_hi,
       W2.city, W2.temp_lo, W2.temp_hi
FROM weather W1, weather W2
WHERE W1.temp_lo < W2.temp_lo
   and W1.temp_hi > W2.temp_hi;


-- 
   Euler Taveira                                   Timbira -
http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento