0

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.

1

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
  • Yes the TaskfieldName is dynamic for a task id. – DB93 May 31 '17 at 9:00
  • check my edited script now. – KumarHarsh May 31 '17 at 9:35
0

Your db is not normalized (2NF) the fields TaskFieldName | TaskFieldValue don't depend on the taskFiledId, they depend on the taskId, so Create At and taskLocation should be on the Task table. And when you need the report, just select it all from the same table.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.