CODEWITHSUNDEEP
mysqlselectdatabase-designmodel
Jens Törnell
Jens Törnell

Reputation: 24778

MySQL - SELECT data and create a "virtual" table

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

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.

Upvotes: 4

Views: 18526

Answers (3)

mjuarez
mjuarez

Reputation: 16834

There's a way to get the data in roughly the same way, just in rows, instead of columns like you posted it.

If you run this query:

   select mt.meta_value, group_concat(pt.value SEPARATOR ", ") 
   from page_table pt,
   meta_table mt
   where pt.meta_id = mt.meta_id
   group by mt.meta_value;

This gets you the data you want, just one "column" per row:

Big headline    Testing value, Text, Another value
Extra headline  New text
Small headline  Small content, Content, Tiny value

Upvotes: 0

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79969

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.

Upvotes: 1

Jacta
Jacta

Reputation: 507

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

Upvotes: 4

Related Questions