Mysql, Create a view from 2 tables

Question

Using mysql, I need some help to create a view using 2 different tables as follow:

Table1 is a liste of item. Table2 is a list of standard types

Output is like (in php style just to give an idea):

|---items--|---types--|---paras---|---description----|

Query=Select * from table1;
while fetching Query in table1{
 Subquery= Select * from table2 where types = table1['types'];
 while fetching Subquery in table2{
  |table1['items']|table1['types']|table2['paras']|table1['description'] : table2['description']|
 }
}

Table1:(pkey is item number)

|---items--|---description----|---types---|
|----------|------------------|-----------|
|item1     | desc1            |  type1    |
|item2     | desc2            |  type1    |
|item3     | desc3            |  type2    |
|item4     | desc4            |  type2    |
|item5     | desc5            |  type3    |

Table2:(Pkey is integer auto increment(not represented in table2))

|---types--|---paras--|---description---|
|----------|----------|-----------------|
|type1     |paraA     | desA            |
|type1     |paraB     | desB            |
|type1     |paraC     | desC            |
|type1     |paraD     | desD            |
|type2     |paraA     | desA            |
|type2     |paraB     | desB            |
|type2     |paraC     | desC            |
|type3     |paraA     | desA            |

The expected output should be like:

|---items--|---types--|---paras---|---description----|
|----------|----------|-----------|------------------|
|item1     |type1     |para1A     | desc1 : des1A    |
|item1     |type1     |para1B     | desc1 : des1B    |
|item1     |type1     |para1C     | desc1 : des1C    |
|item1     |type1     |para1D     | desc1 : des1D    |
|item2     |type1     |para1A     | desc2 : des1A    |
|item2     |type1     |para1B     | desc2 : des1B    |
|item2     |type1     |para1C     | desc2 : des1C    |
|item2     |type1     |para1D     | desc2 : des1D    |
|item3     |type2     |para2A     | desc3 : des2A    |
|item3     |type2     |para2B     | desc3 : des2B    |
|item3     |type2     |para2C     | desc3 : des2C    |
|item4     |type2     |para2A     | desc4 : des2A    |
|item4     |type2     |para2B     | desc4 : des2B    |
|item4     |type2     |para2C     | desc4 : des2C    |
|item5     |type3     |para3a     | desc5 : des3A    |

So I would like a mysql solution to create a view please.

Thanks :)

JOIN was the good solution, I feel tired and don't know how I miss it ... nevermind thanks to you guys :) — Waldon, Feb 14 '18 at 10:10

score 0 · Accepted Answer · answered Feb 14 '18 at 09:53

0

Just use the combination of View and Join query to get an output. for Example Table 1:Employee |---empid--|---name----------|---deptno---| |----------|------------------|-----------|

Table 2: Department |---dpid--|---name------------|---dname---| |----------|------------------|-----------|

The query will be like

Create view [viewname] as select * from Employee join Department on Employee.deptno=Department.dpid;

dpid primary key of Department Table deptno is Foreign Key

Sorry if i am wrong was just trying to help Thank You

answered Feb 14 '18 at 09:53

Anand

16
3

Well that do perfectly the job so a big thanks for you ;) – Waldon Feb 14 '18 at 10:08
Please read the edit help re formatting code (in blocks, suitable also for tables, and quotes). – philipxy Feb 14 '18 at 10:23

score 0 · Answer 2 · answered Feb 14 '18 at 10:11

0

If it's the query that you're looking for it's basically done using JOIN

SELECT t1.items,t1.types,t2.paras,concat(t1.description,' : ',t2.description) AS description 
FROM table1 t1 LEFT JOIN table2 t2 ON t1.types=t2.types;

answered Feb 14 '18 at 10:11

har2vey

676
6
19

Mysql, Create a view from 2 tables

2 Answers2