MySQL - SELECT data and create a "virtual" table

Question

Let's say my database table looks like this:

page_table

| id | page_id | meta_id | value
----------------------------------------
| 1  | 2       | 1       | Testing value
| 2  | 2       | 2       | Small content
| 3  | 2       | 1       | Another value
| 4  | 2       | 2       | Tiny value
| 5  | 3       | 1       | Text
| 6  | 3       | 2       | Content
| 7  | 3       | 3       | New text

meta_table

| id | meta_id | meta_value
-------------------------------
|  1 | 1       | Big headline
|  2 | 2       | Small headline
|  3 | 3       | Extra headline

• "id" is incremental.
• "page_id" is a page id. There can be many metas and values on one page.
• "meta_id" is connected to meta_table.
• "value" can be any string.

Question

Can I create a "virtual" SQL table to easy work with this data?

virtual table - my suggestion

| id | page_id | Big headline  | Small headline | Extra headline
----------------------------------------------------------------
| 1  | 2       | Testing value | Small content  |
| 2  | 2       | Another value | Tiny value     |
| 3  | 3       | Text          | Content        | New text

Above is the same content from both page table and meta table. This is what I want with my SQL.

How far I've come

SELECT * from page_table will not do it this time.

Solution 1

If I understand you correct, then you should read about Create view in mysql, try to search it up, this way its possible to make a "virtual database" - Ex. combine it with some of the code from Mahmoud Gamal

Solution 2

You can achieve this using the following query:

SELECT m.id, p.page_id,
  MAX(CASE WHEN meta_value = 'Big headline' THEN p.value END) AS 'Big headline',
  MAX(CASE WHEN meta_value = 'Small headline' THEN p.value END) AS 'Small headline',
  MAX(CASE WHEN meta_value = 'Extra headline' THEN p.value END) AS 'Extra headline'
FROM meta_table m
LEFT JOIN page_table p ON m.meta_id = p.meta_id
GROUP BY m.id, p.page_id;

You can put this query in a view.

Comments

[MartinG]

The max() function does the magic here, tried without it and didn't work. Can you give a brief explanation/suggestion why?