Postgres Pro
Downloads
Home   >   mailing lists

Thread: sql question

sql question

From
Steven Verhoeven
Date:
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



Re: sql question

From
"Vincent Hikida"
Date:
SELECT  t1.id
              ,  t1.fref
    FROM t1
UNION ALL
SELECT t2.id
              , t2.mref
    FROM t2
 
----- Original Message -----
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



Re: sql question

From
Russell Smith
Date:
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.

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

Re: [NOVICE] sql question

From
"Vincent Hikida"
Date:
OOPs.
 
I mean
 
SELECT  t1.id
              ,  t1.fref
    FROM t1
UNION ALL
SELECT t1.id
              , t1.mref
    FROM t1
----- Original Message -----
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 -----
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



Re: sql question

From
Klint Gore
Date:
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             :                 :
+---------------------------------------+-----------------+

Re: sql question

From
Chris Travers
Date:
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

>
>
> --
> /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
>
>
>