sqlfetchxml

How can I get data from joined tables using fetchxml


I have two tables Tasks and Notes

Tasks

| Id | Task |
|----|------|
| 1  | task1|
| 2  | task2|
| 3  | task3|

Notes

| Id | TaskId | Text |
|----|--------|------|
| 1  | 1      | text1|
| 2  | 1      | text2|
| 3  | 1      | text3|
| 4  | 2      | text4|
| 5  | 2      | text5|
| 6  | 3      | text6|

I want the result of the fetchxml to be:

[ {Id: 1, Task: "task1", Notes: ["text1", "text2", "text3"]},{Id: 2, Task: "task2", Notes: ["text4", "text5"]},{Id: 3, Task: "task3", Notes: ["text6"]}]

I know how to do it using SQL, struggling to find any help to do it using fetchxml can anyone please help me?


Solution

  • SELECT n.TaskId as Id,t.Task,
    group_concat(n.Text) end AS Notes
    FROM tasks t,notes n
    On t.Id=n.TaskId
    WHERE 
    group by n.TaskId,t.Task
    

    I am writing fetchxml below

     Select
         n.TaskId as [@id],
         t.Task as [@Task],
        
        (
        Select
         group_concat(n.Text) as [@Notes],
        
        FROM tasks t,notes n
        On t.Id=n.TaskId
        WHERE 
        group by n.TaskId,t.Task
        FOR XML Raw('notes'),TYPE
        )
        
        FROM tasks t
        WHERE 
        group by t.Id,t.Task
        
        FOR XML PATH('tasks'),
        ROOT('myFetchXml')