Mysql – SELECT Dynamic columns with values from other table

MySQLmysql-5.6optimizationperformancequery-performance

I need some help with a query. I want to select extra columns to a database query that are stored in another table. I will try to explain what I want to do. I have two tables a Task table and a TaskField table. When I query the task table then sometimes I need some extra fields selected from the TaskField table.

+-------------------+ +---------------------------+
|       Tasks       | |        TaskFields         |
+-------------------+ +---------------------------+
| TaskId PK int     | | TaskFieldId PK int        |
| TaskName string   | | TaskId FK -> Tasks.TaskId |
| TaskAuthor string | | TaskFieldName string      |
|                   | | TaskFieldValue string     |
+-------------------+ +---------------------------+

The rows in those tables will look like this. The Tasks table looks as follows

+--------+--------------+------------+
| TaskId |   TaskName   | TaskAuthor |
+--------+--------------+------------+
|      1 | Testing Task | Me         |
+--------+--------------+------------+

The taskfields table will contain this rows.

+-------------+--------+---------------+----------------+
| TaskFieldId | TaskId | TaskFieldName | TaskFieldValue |
+-------------+--------+---------------+----------------+
|           1 |      1 | Created_at    | 2017-31-05     |
|           2 |      1 | TaskLocation  | Berlin         |
+-------------+--------+---------------+----------------+

Now I want to dynamically select the data from taskfields into a row from table so I will get the following output.

+--------+--------------+------------+------------+--------------+
| TaskId |   TaskName   | TaskAuthor | Created_at | TaskLocation |
+--------+--------------+------------+------------+--------------+
|      1 | Testing Task | Me         | 2017-31-05 | Berlin       |
+--------+--------------+------------+------------+--------------+

I have no idea where to begin with this query and I don't even know if it's possible in mysql. I tried to start of with the following query.

SELECT `Tasks`.`TaskId`, `Tasks`.`TaskName`, `Tasks`.`TaskAuthor` FROM `Tasks`
JOIN `TasksFields` ON `Tasks`.`TaskId` = `TasksFields`.`TaskId`

This will return all rows joined but this is not the result I hoped for I need to dynamically append the colums to the result, but I have no idea how to get it done.

Best Answer

what is the logic behind above database design ?

Is TaskfieldName dynamic for particular taskid ?

In Sql Server,

    create table #t (TaskId int,TaskName varchar(50)
 ,TaskAuthor varchar(50) )
insert into #t VALUES(1,'Testing Task','Me')


create table #t1 (TaskFieldId int,TaskId int
 ,TaskFieldName varchar(50), TaskFieldValue varchar(50))
 insert into #t1 VALUES
(1 ,1,'Created_at'   ,'2017-31-05')
,(2 ,1,'TaskLocation' ,'Berlin') 
,(3 ,2,'TaskLocation' ,'Berlin') 
declare @TaskID varchar(10) =1
declare @Taskfield varchar(200)
declare @Sql varchar(max)=''
select top 1 
@Taskfield=stuff((select ','+'['+TaskFieldName +']'
from #t1 t11 where taskid=t1.taskid for xml path('') ),1,1,'')
from #t1 t1
where t1.TaskId=@TaskID
--select @Taskfield
set @Sql=
'SELECT *
FROM (
    SELECT t.*
        ,t1.TaskFieldName
        ,t1.TaskFieldValue
    FROM #t t
    INNER JOIN #t1 t1 ON t.TaskId = t1.TaskId
    where t.taskid='+@TaskID+'
    ) AS src
pivot(max(TaskFieldValue) FOR TaskFieldName IN (
            '+@Taskfield+'
            )) pv;'

--print @Sql
EXEC (@Sql)
drop table #t
drop table #t1
Related Question