[SOLVED] view that has two tables data

[isaac_cm]

Hello,

I need to create a view that get data from two tables like this

 

table_size

-------------

10

12

14

16

 

table_color

------------

Red

Blue

White

Yellow

 

result_view (ordered by size table)

------------------------------------

10 - Red

10 - Blue

10 - White

10 - Yellow

12 - Red

12 - Blue

12 - White

12 - Yellow

etc

.

.

.

 

Thanks

[isaac_cm]

I cant modify my post and I forgot that all the data is in one table like that

 

table1

-----------------

type_id      value

1                10

1                12

1                14

2                Red

2                Blue

2                white

 

I need to combine the data like this

 

result_view (ordered by size table)

------------------------------------

10 - Red

10 - Blue

10 - White

10 - Yellow

12 - Red

12 - Blue

12 - White

12 - Yellow

etc

.

.

.

 

thanks

[Illusion]

Try this

Create view v as select concat_ws('-',a.value,b.value) from table1 as a, table1 as b where a.value REGEXP '[0-9]*' and b.value NOT REGEXP '[0-9]*' 

[isaac_cm]

sorry did not work, not all values in same type has numbers like 1,2,3 some of them has string values like xl, xxl, etc...

 

I want to combine both of them based on type_id value

 

Thanks

[Illusion]

I understand that, what the query returned.

[isaac_cm]

nothing !!!

[Illusion]

How does type_id helps do combine that numbers, strings?

what I understand is you want all Cartesian product matchings of that numbers and strings.

[isaac_cm]

some of them has type_id = 1 and others has type_id = 2 , thanks I found a solution with a friend help Alan Larkin

 

SELECT distinct concat(t1.value, '-', t2.value) as attr,

t1.type_id FROM

table1 AS t1 INNER JOIN table1 AS t2

ON t1.type_id + 1 = t2.type_id

 

thanks for your effort