Joining tables MySQL

Question

If I have the following tables

tbl1:

+------+-----------+
| sex  | type      |
+------+-----------+
|  m   | rolls     |
|  f   | acom      |
+------+-----------+

tbl2:

+------+-----------+
| age  | type      |
+------+-----------+
| 12   | rolls     |
| 25   | acom      |
+------+-----------+

How do I create a view to show the two tables merged but, sex and age become category

+-----------+-----------+
| category  | type      |
+-----------+-----------+
|    m      | rolls     |
|    f      | acom      |
|   12      | rolls     |
|   25      | acom      |
+-----------+-----------+

thanks.

score 0 · Accepted Answer · answered Dec 18 '13 at 05:15

0

You can use a union statement

SELECT `sex` as `category`,`type` FROM `tbl1`
UNION
SELECT `age` as `category`,`type` FROM `tbl2`

answered Dec 18 '13 at 05:15

Robbert

6,481
5
35
61

THAAAAAANKS, it work perfectly. It's there a way to change the Type of categorie to VARCHAR? – JoseMiguel Dec 18 '13 at 05:28

score 0 · Answer 2 · answered Dec 18 '13 at 05:15

0

you can that by using a union

select sex as category, type from tbl1
union
select age as category, type from tbl2

answered Dec 18 '13 at 05:15

Scary Wombat

44,617
6
35
64

score 0 · Answer 3 · answered Dec 18 '13 at 05:17

0

Try this Union all merge two tables http://dev.mysql.com/doc/refman/5.0/en/union.html

select sex as category, type from table1
union all
select age as category, type from table2

answered Dec 18 '13 at 05:17

naveen goyal

4,571
2
16
26

Aziz Shaikh · Answer 4 · 2013-12-18T05:26:36.853

0

Better to CAST and then do UNION so that columns can have same datatype, like this:

CREATE VIEW `view1` AS
SELECT CAST(`sex` as CHAR(50)) AS `category`, `type` FROM `tbl1`
UNION
SELECT CAST(`age` as CHAR(50)) AS `category`, `type` FROM `tbl2`;

In some earlier versions of MySQL, only the type and length from the first SELECT would have been used. http://dev.mysql.com/doc/refman/5.0/en/union.html

edited Dec 18 '13 at 05:26

answered Dec 18 '13 at 05:20

Aziz Shaikh

16,245
11
62
79

It's there a way to change the Type of categorie to VARCHAR? I tried: SELECT CAST(`sex` as VARCHAR(10)) AS `category`, `type` FROM `tbl1` But throws an error – JoseMiguel Dec 18 '13 at 05:35
@user2611352 No, it cannot be converted to VARCHAR. Reference: http://stackoverflow.com/a/15368852/ – Aziz Shaikh Dec 18 '13 at 05:38

score 0 · Answer 5 · answered Dec 18 '13 at 05:24

Try this query

create view v1 as 
select cast(sex as char(10)) as category, type from tbl1
union
select cast(age as char(10)) as category, type from tbl2
;

Results

| CATEGORY |  TYPE |
|----------|-------|
|        m | rolls |
|        f |  acom |
|       12 | rolls |
|       25 |  acom |

Joining tables MySQL

5 Answers5

Results