Hi all,
I have to create a view. For this, a table needs to transformed like this:
So the original table is
1 a b
2 c d
I want
1 a
1 b
2 c
2 d
I can do two queries giving me this, but how do I combine them in a view? If a
new table would need to be created, I could do a select into - but in a view?
It would need to be done in one query, if I understood it right.
Anybody an idea?
Thanks,
Stefan
--
Stefan Kuhn BSc MA
IPB Halle
AG Bioinformatik & Massenspektrometrie
Weinberg 3
06120 Halle
http://www.ipb-halle.de http://msbi.bic-gh.de
skuhn@ipb-halle.de*Tel. +49 (0) 345 5582 1474*Fax.+49 (0) 345 5582 1409

drop table if exists test.flipdata;
create table test.flipdata (f1 int,f2 char(1),f3 char(1));
insert into test.flipdata values (1,'a','b'),(2,'c','d');
create view test.RotatedData as select f1,f2 from test.flipdata union select f1,f3 from test.flipdata order by 1;
select * from test.RotatedData;

----- Original Message -----
From: "Stefan Kuhn" <skuhn@ipb-halle.de>
To: "MySQL" <mysql@lists.mysql.com>
Sent: Monday, July 16, 2007 8:58:58 AM (GMT-0500) America/New_York
Subject: Putting two queries in one

Hi all,
I have to create a view. For this, a table needs to transformed like this:
So the original table is
1 a b
2 c d
I want
1 a
1 b
2 c
2 d
I can do two queries giving me this, but how do I combine them in a view? If a
new table would need to be created, I could do a select into - but in a view?
It would need to be done in one query, if I understood it right.
Anybody an idea?
Thanks,
Stefan
--
Stefan Kuhn BSc MA
IPB Halle
AG Bioinformatik & Massenspektrometrie
Weinberg 3
06120 Halle
http://www.ipb-halle.de http://msbi.bic-gh.de
skuhn@ipb-halle.deÂ*Tel. +49 (0) 345 5582 1474Â*Fax.+49 (0) 345 5582 1409

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=redwards@swmx.com

drop table if exists test.flipdata;
drop view if exists test.RotatedData;
create table test.flipdata (f1 int,f2 char(1),f3 char(1));
insert into test.flipdata values (1,'a','b'),(2,'c','d');
create view test.RotatedData as select f1,f2 from test.flipdata union select f1,f3 from test.flipdata order by 1;
select * from test.RotatedData;

These are the results I got running MySQL 5.0.45 in Windows:

mysql> drop table if exists test.flipdata;
Query OK, 0 rows affected (0.00 sec)

mysql> drop view if exists test.RotatedData;
Query OK, 0 rows affected (0.00 sec)

mysql> create table test.flipdata (f1 int,f2 char(1),f3 char(1));
Query OK, 0 rows affected (0.06 sec)

mysql> insert into test.flipdata values (1,'a','b'),(2,'c','d');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> create view test.RotatedData as select f1,f2 from test.flipdata union select f1,f3 from test.flipdata order by 1;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test.RotatedData;
+------+------+
| f1 | f2 |
+------+------+
| 1 | a |
| 1 | b |
| 2 | c |
| 2 | d |
+------+------+
4 rows in set (0.00 sec)

----- Original Message -----
From: "Rolando Edwards" <redwards@swmx.com>
To: "Stefan Kuhn" <skuhn@ipb-halle.de>
Cc: "MySQL" <mysql@lists.mysql.com>
Sent: Monday, July 16, 2007 9:44:48 AM (GMT-0500) America/New_York
Subject: Re: Putting two queries in one

drop table if exists test.flipdata;
create table test.flipdata (f1 int,f2 char(1),f3 char(1));
insert into test.flipdata values (1,'a','b'),(2,'c','d');
create view test.RotatedData as select f1,f2 from test.flipdata union select f1,f3 from test.flipdata order by 1;
select * from test.RotatedData;

----- Original Message -----
From: "Stefan Kuhn" <skuhn@ipb-halle.de>
To: "MySQL" <mysql@lists.mysql.com>
Sent: Monday, July 16, 2007 8:58:58 AM (GMT-0500) America/New_York
Subject: Putting two queries in one

Hi all,
I have to create a view. For this, a table needs to transformed like this:
So the original table is
1 a b
2 c d
I want
1 a
1 b
2 c
2 d
I can do two queries giving me this, but how do I combine them in a view? If a
new table would need to be created, I could do a select into - but in a view?
It would need to be done in one query, if I understood it right.
Anybody an idea?
Thanks,
Stefan
--
Stefan Kuhn BSc MA
IPB Halle
AG Bioinformatik & Massenspektrometrie
Weinberg 3
06120 Halle
http://www.ipb-halle.de http://msbi.bic-gh.de
skuhn@ipb-halle.deÂ*Tel. +49 (0) 345 5582 1474Â*Fax.+49 (0) 345 5582 1409

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=redwards@swmx.com



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=redwards@swmx.com