DataGrids and MySQL join?

[JP2006]

I have a situation where I have 2 tables - NODES and TAGS. Nodes is the
main table and contains various columns one of which is tagID. Tags
contains a list of tag names and tagIDs. When a user adds an entry to
the Nodes table they can specify one or more tags for the entry. Then
when the form is posted if there were multiple tags in the post their
tagIDs are comma seperated before being inserted in the tagID column of
the Nodes table.
This all works fine and dandy.

The issue is that I then want to be able to display a list of Nodes
with their details including which tags are associated with them.
To display most of the Node info is easy and I am currently binding a
DataReader to a DataGrid to do this. However I need now to show the
tags for each Node so need to do some type of join in MySQL (I assume)
to get the tag names from their tagIDs. But this is further complicated
by the fact that each node may have comma seperated, mulitple tagIDs
associated with it and I need to put those into an array (or similar)
first in order to be able to treat them as integers before being able
to do much with them.
I'm sure I could write some custom code, ditching the datagrid, in
order to do this but am wondering whether there is a better way to do
it using other .NET objects - or whether the answer lies with SQL.

Any ideas?

[clickon]

Are your tables one to one realted or one to many, i.e. does each nodes
record have one tags record containing multiple comma seperated values or
does each tag have it's own record.

If it is the latter then you can put the two tables in a DataSet create a
relation and then use the GetChildRows method for each row in the Nodes
record set and it will return an array of DataRows for the realted tags.

"JP2006" wrote:

I have a situation where I have 2 tables - NODES and TAGS. Nodes is the
main table and contains various columns one of which is tagID. Tags
contains a list of tag names and tagIDs. When a user adds an entry to
the Nodes table they can specify one or more tags for the entry. Then
when the form is posted if there were multiple tags in the post their
tagIDs are comma seperated before being inserted in the tagID column of
the Nodes table.
This all works fine and dandy.

The issue is that I then want to be able to display a list of Nodes
with their details including which tags are associated with them.
To display most of the Node info is easy and I am currently binding a
DataReader to a DataGrid to do this. However I need now to show the
tags for each Node so need to do some type of join in MySQL (I assume)
to get the tag names from their tagIDs. But this is further complicated
by the fact that each node may have comma seperated, mulitple tagIDs
associated with it and I need to put those into an array (or similar)
first in order to be able to treat them as integers before being able
to do much with them.
I'm sure I could write some custom code, ditching the datagrid, in
order to do this but am wondering whether there is a better way to do
it using other .NET objects - or whether the answer lies with SQL.

Any ideas?

[JP2006]

The first; each node record has one tagID record potentially containing
many comma seperated tagIDs.
I have since written some code, without a DataGrid, using 2 arrays to
capture the node table data and the tags table data and then compare
the 2 on the tagID column. It works although it is a bit lengthy!

clickon wrote:

Are your tables one to one realted or one to many, i.e. does each nodes
record have one tags record containing multiple comma seperated values or
does each tag have it's own record.

If it is the latter then you can put the two tables in a DataSet create a
relation and then use the GetChildRows method for each row in the Nodes
record set and it will return an array of DataRows for the realted tags.

"JP2006" wrote:

I have a situation where I have 2 tables - NODES and TAGS. Nodes is the
main table and contains various columns one of which is tagID. Tags
contains a list of tag names and tagIDs. When a user adds an entry to
the Nodes table they can specify one or more tags for the entry. Then
when the form is posted if there were multiple tags in the post their
tagIDs are comma seperated before being inserted in the tagID column of
the Nodes table.
This all works fine and dandy.

The issue is that I then want to be able to display a list of Nodes
with their details including which tags are associated with them.
To display most of the Node info is easy and I am currently binding a
DataReader to a DataGrid to do this. However I need now to show the
tags for each Node so need to do some type of join in MySQL (I assume)
to get the tag names from their tagIDs. But this is further complicated
by the fact that each node may have comma seperated, mulitple tagIDs
associated with it and I need to put those into an array (or similar)
first in order to be able to treat them as integers before being able
to do much with them.
I'm sure I could write some custom code, ditching the datagrid, in
order to do this but am wondering whether there is a better way to do
it using other .NET objects - or whether the answer lies with SQL.

Any ideas?