sql - Querying a MySQL database to get values from another table -

i have 2 mysql database tables:

motorhome

----------------------- | id  | status | name | ----------------------- |  1  | active | mh 1 | |  2  | active | mh 2 | ----------------------- 

motorhome_meta

---------------------------------------------- | id  | motorhome_id | meta_key | meta_value | ---------------------------------------------- |  1  | 1            | size     | large      | |  2  | 1            | fuel     | petrol     | |  3  | 2            | size     | small      | |  4  | 2            | fuel     | petrol     | ---------------------------------------------- 

what need query allow me select row motorhome table, values motorhome_meta table. or in other words if selecting row in motorhome table id 1.

---------------------------------------- | id  | status | name | size  | fuel   | ---------------------------------------- |  1  | active | mh 1 | large | petrol | ---------------------------------------- 

there occasions might need use statement against either table eg where motorhome.status='active' or where motorhome_meta.meta_key='size'.

you can use aggregate function case create columns rows of data:

select m.id,   m.status,   m.name,   max(case when h.meta_key= 'size' h.meta_value end) size,   max(case when h.meta_key= 'fuel' h.meta_value end) fuel motorhome m inner join motorhome_meta h   on m.id = h.motorhome_id -- m.id = 1 group   m.id, m.status, m.name 

see sql fiddle demo. gives result:

| id | status | name |  size |   fuel | --------------------------------------- |  1 | active | mh 1 | large | petrol | |  2 | active | mh 2 | small | petrol | 

---

you can use variation, if there unique constraint on (motorhome_id, meta_key) combination:

select m.id,   m.status,   m.name,   h1.meta_value  size,   h2.meta_value  fuel motorhome m left join motorhome_meta h1           -- or:  inner join   on  m.id = h1.motorhome_id          -- depending on need (or not)   , h1.meta_key = 'size'            -- show motorhomes left join motorhome_meta h2           -- have  'fuel'   on  m.id = h2.motorhome_id          -- or 'size'   , h2.meta_key = 'fuel' ;  

see: sql-fiddle-2