mysql - Select the name and value of a colmun using 2 different inner joined columns' values -

i'm not sure if mysql capable of doing this. i'm no mysql master means me seems might example of pivot table?

i've looked @ other pivot table example , find them confusing @ best , i'm still not quite sure if it's relevant in case.

here's example of data , tables in question:

foo table:

rowid | col_one | col_two | col_three ------|---------|---------|----------- 1     | 2       | 34      | 64 2     | 6       | 53      | 23 3     | 8       | 22      | 45 

foo_meta table:

rowid | fooid | metaname | metavalue ------|-------|----------|----------- 1     | 1     |     | 302 2     | 1     |     | 466 3     | 1     | other    | 132 4     | 2     |     | 222 5     | 2     |     | 87 6     | 2     | other    | 400 7     | 3     |     | 732 8     | 3     |     | 55 9     | 3     | other    | 690 

here's example of select i'm using isn't quite i'm looking for:

select:

select     t.col_one,     t.col_two,     t.col_three,     m.metaname,     m.metavalue     foo t inner join     foo_meta m on t.rowid = m.fooid 

here's example of table i'm trying select:

select:

rowid | col_one | col_two | col_three | | | other ------|---------|---------|--------------------------------- 1     | 2       | 34      | 64        | 302  | 466  | 132 2     | 6       | 53      | 23        | 222  | 87   | 400 3     | 8       | 22      | 45        | 732  | 55   | 690 

yes, pivoting foo_meta fields - @ least versions of oracle that, i'm quite mysql doesn't.

generally, "fake" pivot in mysql, multiple joins. like:

select     t.col_one,     t.col_two,     t.col_three,     mthis.this,     mthat.that,     mother.other     foo t inner join     (select fooid, metavalue foo_meta metaname = 'this') mthis on t.rowid = mthis.fooid inner join     (select fooid, metavalue foo_meta metaname = 'that') mthat on t.rowid = mthat.fooid inner join     (select fooid, metavalue other foo_meta metaname = 'other') mother on t.rowid = mother.fooid 

you might have use left outers depending on how data populated.