problem setting the datasourse for a treeview

Question

problem setting the datasourse for a treeview

kodekeeper

SSC Rookie

Points: 37
More actions
April 7, 2011 at 8:42 am

#237464

Hi i have two tables and i want to populate a treeview using a left join query but i need a unique root node and multiple child nodes:
drop table wf
drop table wfstate
CREATE TABLE [dbo].[wf](
[WorkflowID] [int] IDENTITY NOT NULL,
[WorkflowName] [varchar](50) NOT NULL)
GO
CREATE TABLE [dbo].[wfstate](
[WorkflowStateID] [int] IDENTITY NOT NULL,
[WorkflowID] [int] NOT NULL,
[State] [varchar](50) NOT NULL,
[ParentID] [int] NOT NULL)
GO
INSERT wf(WorkflowName) VALUES('workflow1')
INSERT wf(WorkflowName) VALUES('workflow2')
INSERT wfstate(WorkflowID,State,ParentID) VALUES('1','estado1','1')
INSERT wfstate(WorkflowID,State,ParentID) VALUES('1','estado2','1')
INSERT wfstate(WorkflowID,State,ParentID) VALUES('2','estado1','2')
INSERT wfstate(WorkflowID,State,ParentID) VALUES('2','estado2','2')
INSERT wfstate(WorkflowID,State,ParentID) VALUES('2','estado3','2')
select wf.workflowid,
wf.workflowname,
wfstate.parentid from wf
left join wfstate on wf.workflowid = wfstate.workflowid
this is the left join:
select wf.workflowid,
wf.workflowname,
wfstate.parentid from wf
left join wfstate on wf.workflowid = wfstate.workflowid
this query return duplicate "workflowid" (root node) does anyone have any ideas to acomplish this task, maybe the tables are bad designed or this can be accomplished using FOR XML retrieving something like
<row>
<workflowid>1</workflowid>
<workflowname>workflow1</workflowname>
<parentid>1</parentid>
</row>
<row>
<workflowname>workflow1</workflowname>
<parentid>1</parentid>
</row>
<row>
<workflowid>2</workflowid>
<workflowname>workflow2</workflowname>
<parentid>2</parentid>
</row>
<row>
<workflowname>workflow2</workflowname>
<parentid>2</parentid>
</row>
<row>
<workflowname>workflow2</workflowname>
<parentid>2</parentid>
</row>

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

jerry-621596 SSCertifiable Points: 6096 More actions · Answer 1

The query is not returning duplicate values, it is returning exactly what you requested, and only one of each physical instance in the db.

To see any unique data, you will need to include the State column. That is the only unique data in your entire dataset.

kodekeeper SSC Rookie Points: 37 More actions · Answer 2

Thanks for the response, the problem is that the treeview is especting an unique root in this case is workflowid, using this query with "FOR XML AUTO" the result continues retrieving duplicate workflowids... the treeview uses the parentid to identify the parent node...

select wf.workflowid,

wf.workflowname,

wfstate.state,

wfstate.parentid from wf

left join wfstate on wf.workflowid = wfstate.workflowid

for xml auto

this is the xml retrieved <wf workflowid="1" workflowname="workflow1">

<wfstate state="estado1" parentid="1" />

<wfstate state="estado2" parentid="1" />

</wf>

<wf workflowid="2" workflowname="workflow2">

<wfstate state="estado1" parentid="2" />

<wfstate state="estado2" parentid="2" />

<wfstate state="estado3" parentid="2" />

</wf>

kodekeeper SSC Rookie Points: 37 More actions · Answer 3

Ive fixed it i was needing to put the "root"

thanks!