sql question

SELECT t1.id
, t1.fref
FROM t1
UNION ALL
SELECT t2.id
, t2.mref
FROM t2

----- Original Message -----
From: Steven Verhoeven
To: pgsql-general@postgresql.org ; pgsql-novice@postgresql.org
Sent: Friday, March 11, 2005 4:36 AM
Subject: [GENERAL] sql question

Hi all

My table definition :

id | fref | mref
------+-----------+----------
1 | 23 | 25
2 | 24 | 28
3 | 25 | 31
4 | 26 | 34

My problem :
i need a query that results in this :

id | ref
------+----------
1 | 23
1 | 25
2 | 24
2 | 28
3 | 25
3 | 31
4 | 26
4 | 34

Do I need a crosstab-query ?
Who can help me ?

--
A computer is like an airconditioner. When windows open, it stops working !
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

Steven Verhoeven, ICT Support Engineer

Department for Molecular Biomedical Research (DMBR)
VIB - Ghent University 'Fiers-Schell-Van Montagu' building
Technologiepark 927B - 9052 Ghent (Zwijnaarde)
Belgium
Tel : +32-(0)9-33-13.606
Fax : +32-(0)9-33-13.609 E-mail : Steven.Verhoeven@dmbr.UGent.be
URL : http://www.dmbr.UGent.be

On Fri, 11 Mar 2005 11:36 pm, Steven Verhoeven wrote:

Hi all

My table definition :

id | fref | mref
------+-----------+----------
1 | 23 | 25
2 | 24 | 28
3 | 25 | 31
4 | 26 | 34

My problem :
i need a query that results in this :

id | ref
------+----------
1 | 23
1 | 25
2 | 24
2 | 28
3 | 25
3 | 31
4 | 26
4 | 34

SELECT id, fref as ref FROM table
UNION ALL
SELECT id, mref as ref FROM table;

Should do the trick.

OOPs.

I mean

SELECT t1.id
, t1.fref
FROM t1
UNION ALL
SELECT t1.id
, t1.mref
FROM t1
----- Original Message -----
From: Vincent Hikida
To: Steven Verhoeven ; pgsql-general@postgresql.org ; pgsql-novice@postgresql.org
Sent: Sunday, March 13, 2005 6:34 PM
Subject: Re: [NOVICE] [GENERAL] sql question

SELECT t1.id
, t1.fref
FROM t1
UNION ALL
SELECT t2.id
, t2.mref
FROM t2

----- Original Message -----
From: Steven Verhoeven
To: pgsql-general@postgresql.org ; pgsql-novice@postgresql.org
Sent: Friday, March 11, 2005 4:36 AM
Subject: [GENERAL] sql question

Hi all

My table definition :

id | fref | mref
------+-----------+----------
1 | 23 | 25
2 | 24 | 28
3 | 25 | 31
4 | 26 | 34

My problem :
i need a query that results in this :

id | ref
------+----------
1 | 23
1 | 25
2 | 24
2 | 28
3 | 25
3 | 31
4 | 26
4 | 34

Do I need a crosstab-query ?
Who can help me ?

--
A computer is like an airconditioner. When windows open, it stops working !
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

Steven Verhoeven, ICT Support Engineer

Department for Molecular Biomedical Research (DMBR)
VIB - Ghent University 'Fiers-Schell-Van Montagu' building
Technologiepark 927B - 9052 Ghent (Zwijnaarde)
Belgium
Tel : +32-(0)9-33-13.606
Fax : +32-(0)9-33-13.609 E-mail : Steven.Verhoeven@dmbr.UGent.be
URL : http://www.dmbr.UGent.be

On Fri, 11 Mar 2005 13:36:17 +0100, Steven Verhoeven
<Steven.Verhoeven@dmbr.UGent.be> wrote:

Hi all

My table definition :

id | fref | mref
------+-----------+----------
1 | 23 | 25
2 | 24 | 28
3 | 25 | 31
4 | 26 | 34

My problem :
i need a query that results in this :

id | ref
------+----------
1 | 23
1 | 25
2 | 24
2 | 28
3 | 25
3 | 31
4 | 26
4 | 34

Do I need a crosstab-query ?

select id, fref from mytable
union all
select id, mref from mytable
order by 1,2

klint.

+---------------------------------------+-----------------+
: Klint Gore                            : "Non rhyming    :
: EMail   : kg@kgb.une.edu.au           :  slang - the    :
: Snail   : A.B.R.I.                    :  possibilities  :
: Mail      University of New England   :  are useless"   :
:           Armidale NSW 2351 Australia :     L.J.J.      :
: Fax     : +61 2 6772 5376             :                 :
+---------------------------------------+-----------------+

Steven Verhoeven wrote:

Hi all

My table definition :

id | fref | mref
------+-----------+----------
1 | 23 | 25
2 | 24 | 28
3 | 25 | 31
4 | 26 | 34

My problem :
i need a query that results in this :

id | ref
------+----------
1 | 23
1 | 25
2 | 24
2 | 28
3 | 25
3 | 31
4 | 26
4 | 34

Do I need a crosstab-query ?
Who can help me ?

How about
select id, mref AS ref from table
UNION
select id, fref AS ref from table

Or is that not what you want?

Best Wishes,
Chris Travers
Metatron Technology Consulting