Thank you for the input John. You understood my sketch just fine and your JOIN is indeed equivalent to the nested select. I said there is no relationship, but in my nested select I implicitly created a relationship. I should have been more explicit here: what I meant is that there "should" be no relationship. >From what I know of SQL, one always needs a relationship to append some row to the one from FROM clause. I want to append them without a relationship. So if my base table "t" has columns (time and data), I want a new table which has columns (time2008, data2008, time2009, data2009, time2010, data2010,...) where rows of time2009 and data2009 are constrained by 'year 2008' , but are in no relationship with the rows of time2008. (NULL should be used if there are more in year2008 column, than in year2009 column, vice versa.) Regards, Davor "John R Pierce" <pierce@xxxxxxxxxxxx> wrote in message news:4B72729D.7020302@xxxxxxxxxxxxxxx > Davor J. wrote: >> Let's say you have a table: >> CREATE TABLE t ( >> time date, >> data integer >> ) >> >> Suppose you want a new table that has columns similar to the following: >> "(x.time, x.data, y.time, y.data, z.time, z.data)" where x.time, y.time >> and z.time columns are constrained (for example x.time >2007 AND x.time >> <2008, y.time >2008 AND y.time < 2009, z.time > 2010) >> >> How would you do this. Note that you can not use JOIN as there is no >> relationship. >> >> Currently I came up with something like this: >> >> SELECT X.*, (SELECT Y.time, Y.data FROM t AS Y WHERE Y.time = X.time + >> 1), (SELECT Z.time .) FROM t AS X WHERE X.time >2007 AND X.time <2008 >> > > > Um, why can't you use a join? > > SELECT X.*, Y.time, Y.data FROM t AS X JOIN t as Y ON (Y.time = X.time + > '1 year'::INTERVAL) WHERE X.time >= '2007-01-01'::DATE AND X.time < > '2008-01-01'::DATE; > > > > I believe should be functionally equivalent to your nested select. I'm > not real sure what you're trying to imply with your date > integer > comparisions, so I tried to be a little more rigorous there. > > > > -- > Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
