sql - How to get two columns from mysql dynamic table

Question

I have a dynamic table which created by breezing form

>   id  record  element title   name    type    value
>   6131    627 6448    Date    date    Calendar    2017-05-15
>   6132    627 6453    Number  num Text    4
>   6129    626 6448    Date    date    Calendar    2017-05-12
>   6130    626 6453    Number  num Text    3
>   6127    625 6448    Date    date    Calendar    2017-05-10
>   6128    625 6453    Number  num Text    1

I want to create a chart by joomla-plotalot components which ask 2 columns result within one select query,like

> 2017-05-15    1 
> 2017-05-12    3 
> 2017-05-10    4

is it possible?

Answer 1

There is a simple solution. Join the table to itself using subqueries with aliases.

SELECT 
  d.`value` AS `date`,
  n.`value` AS `num`
FROM
  (SELECT `record`, `value` FROM MyTable WHERE `name`='date') AS d
  INNER JOIN
  (SELECT `record`, `value` FROM MyTable WHERE `name`='num') AS n
  USING (`record`);