Join two datatables populated from different sources?

microsoft.public.dotnet.framework.adonet

5 Apr 2005 6:35 PM

Dave

Hi,

I've managed to populate a dataset with two different datatables from
different webservices. They each contain a different number of columns but
both have the Ticker symbol as a common key.

Is there a way to "join" or combine these the two tables based on the ticker
into one? I only want to return rows from each where there is a match on
ticker between the two (like an inner join).

I've tried creating a DataRelation but the datatable I had setup as the
parent returns all it's rows when I bind it to a datagrid even though there
are no matching children. So, I'm not sure if this object is the way to go.

If I'm able to combine the datatables into a single one, I want to filter it
at that point.

Thanks, Dave

5 Apr 2005 6:39 PM

Alex Passos

Yes you can merge using the DataSets merge method. Here is the MSDN
documentation for it:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemDataDataSetClassMergeTopic.asp

Show quoteHide quote

"Dave" <D***@discussions.microsoft.com> wrote in message
news:5C1DE540-70F3-4D00-83D9-8C529D35D183@microsoft.com...
> Hi,
>
> I've managed to populate a dataset with two different datatables from
> different webservices. They each contain a different number of columns
> but
> both have the Ticker symbol as a common key.
>
> Is there a way to "join" or combine these the two tables based on the
> ticker
> into one? I only want to return rows from each where there is a match on
> ticker between the two (like an inner join).
>
> I've tried creating a DataRelation but the datatable I had setup as the
> parent returns all it's rows when I bind it to a datagrid even though
> there
> are no matching children. So, I'm not sure if this object is the way to
> go.
>
> If I'm able to combine the datatables into a single one, I want to filter
> it
> at that point.
>
> Thanks, Dave
>
>

Are all your drivers up to date? click for free checkup

5 Apr 2005 7:41 PM

Dave

Thanks, but this doesn't seem to work for me. The only column in common
between the two is ticker. How does it know to join on it? I tried merging
the second datatable into the first but nothing different was displayed when
I bound it.

dst.Merge(dst.Tables["Company2"], false,MissingSchemaAction.Add);

DataGrid1.DataSource = dst.Tables[0];
DataGrid1.DataBind(); --still shows just the original "Compan1"...

Dave

Show quoteHide quote

"Alex Passos" wrote:

> Yes you can merge using the DataSets merge method. Here is the MSDN
> documentation for it:
>
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemDataDataSetClassMergeTopic.asp
>
> "Dave" <D***@discussions.microsoft.com> wrote in message
> news:5C1DE540-70F3-4D00-83D9-8C529D35D183@microsoft.com...
> > Hi,
> >
> > I've managed to populate a dataset with two different datatables from
> > different webservices. They each contain a different number of columns
> > but
> > both have the Ticker symbol as a common key.
> >
> > Is there a way to "join" or combine these the two tables based on the
> > ticker
> > into one? I only want to return rows from each where there is a match on
> > ticker between the two (like an inner join).
> >
> > I've tried creating a DataRelation but the datatable I had setup as the
> > parent returns all it's rows when I bind it to a datagrid even though
> > there
> > are no matching children. So, I'm not sure if this object is the way to
> > go.
> >
> > If I'm able to combine the datatables into a single one, I want to filter
> > it
> > at that point.
> >
> > Thanks, Dave
> >
> >
>
>
>

5 Apr 2005 7:35 PM

Sahil Malik [MVP]

Okay so if I understood your question correctly,

You have two tables. They have a common column. And you want to find all
rows, where that particular column value is the same in either table. Right?

Hmm ... Merge is not what you need then .. IMO.

You can however use DataTable.Select. The where clause you formualte for the
the Select method, will have to be formed over a loop from the first table.

So something like ..

Table2.Select("Ticker In (1,2,3,4)")

Where 1,2,3,4 are formed over a loop from Table1.Ticker.

BTW - I must add - DataTable.Select isn't the fastest pony in the stable, so
hopefully you don't have a lot of rows.

There are other ways to do this too - but the above is the most
straightforward.

- Sahil Malik [MVP]
http://codebetter.com/blogs/sahil.malik/

Show quoteHide quote

"Dave" <D***@discussions.microsoft.com> wrote in message
news:5C1DE540-70F3-4D00-83D9-8C529D35D183@microsoft.com...
> Hi,
>
> I've managed to populate a dataset with two different datatables from
> different webservices. They each contain a different number of columns
but
> both have the Ticker symbol as a common key.
>
> Is there a way to "join" or combine these the two tables based on the
ticker
> into one? I only want to return rows from each where there is a match on
> ticker between the two (like an inner join).
>
> I've tried creating a DataRelation but the datatable I had setup as the
> parent returns all it's rows when I bind it to a datagrid even though
there
> are no matching children. So, I'm not sure if this object is the way to
go.
>
> If I'm able to combine the datatables into a single one, I want to filter
it
> at that point.
>
> Thanks, Dave
>
>

5 Apr 2005 7:47 PM

Dave

Thanks! You're right. It would be just like a SQL Join on ticker, except I
have these two datatables loaded with XML from external separate sources. I
want to join them into one result set somehow.

I'd be interested in other ideas too. When I started this, I thought the
hard part was getting the datatables loaded but now I'm kind of stuck getting
getting them joined/combined on the common key.

Thanks, Dave

Show quoteHide quote

"Sahil Malik [MVP]" wrote:

> Okay so if I understood your question correctly,
>
> You have two tables. They have a common column. And you want to find all
> rows, where that particular column value is the same in either table. Right?
>
> Hmm ... Merge is not what you need then .. IMO.
>
> You can however use DataTable.Select. The where clause you formualte for the
> the Select method, will have to be formed over a loop from the first table.
>
> So something like ..
>
> Table2.Select("Ticker In (1,2,3,4)")
>
> Where 1,2,3,4 are formed over a loop from Table1.Ticker.
>
> BTW - I must add - DataTable.Select isn't the fastest pony in the stable, so
> hopefully you don't have a lot of rows.
>
> There are other ways to do this too - but the above is the most
> straightforward.
>
> - Sahil Malik [MVP]
> http://codebetter.com/blogs/sahil.malik/
>
>
>
> "Dave" <D***@discussions.microsoft.com> wrote in message
> news:5C1DE540-70F3-4D00-83D9-8C529D35D183@microsoft.com...
> > Hi,
> >
> > I've managed to populate a dataset with two different datatables from
> > different webservices. They each contain a different number of columns
> but
> > both have the Ticker symbol as a common key.
> >
> > Is there a way to "join" or combine these the two tables based on the
> ticker
> > into one? I only want to return rows from each where there is a match on
> > ticker between the two (like an inner join).
> >
> > I've tried creating a DataRelation but the datatable I had setup as the
> > parent returns all it's rows when I bind it to a datagrid even though
> there
> > are no matching children. So, I'm not sure if this object is the way to
> go.
> >
> > If I'm able to combine the datatables into a single one, I want to filter
> it
> > at that point.
> >
> > Thanks, Dave
> >
> >
>
>
>

5 Apr 2005 7:52 PM

Sahil Malik [MVP]

Another approach could be to have a seperate table with all the unique
ticker values, and then setup a relationship with the two tables.

TickerMapId, TIcker1, Ticker2.

And once you setup this relation, (This'd be somewhat like a nullable FK),
you can find where Ticker1 = Ticker2 - and find childrows based on that.

This approach has an initial setup cost - but will work MUCH faster than
DataTable.Select, and is more suitable to a situation where you have
multiple requests to the same two datatables you've prepared.

I am sure there are even other approaches - it totally depends on how
creative you wanna be. :)

- Sahil Malik [MVP]
http://codebetter.com/blogs/sahil.malik/

Show quoteHide quote

"Dave" <D***@discussions.microsoft.com> wrote in message
news:7C0A0F02-9BA6-43E6-81E2-616E82CA3216@microsoft.com...
> Thanks! You're right. It would be just like a SQL Join on ticker, except I
> have these two datatables loaded with XML from external separate sources.
I
> want to join them into one result set somehow.
>
> I'd be interested in other ideas too. When I started this, I thought the
> hard part was getting the datatables loaded but now I'm kind of stuck
getting
> getting them joined/combined on the common key.
>
> Thanks, Dave
>
> "Sahil Malik [MVP]" wrote:
>
> > Okay so if I understood your question correctly,
> >
> > You have two tables. They have a common column. And you want to find all
> > rows, where that particular column value is the same in either table.
Right?
> >
> > Hmm ... Merge is not what you need then .. IMO.
> >
> > You can however use DataTable.Select. The where clause you formualte for
the
> > the Select method, will have to be formed over a loop from the first
table.
> >
> > So something like ..
> >
> > Table2.Select("Ticker In (1,2,3,4)")
> >
> > Where 1,2,3,4 are formed over a loop from Table1.Ticker.
> >
> > BTW - I must add - DataTable.Select isn't the fastest pony in the
stable, so
> > hopefully you don't have a lot of rows.
> >
> > There are other ways to do this too - but the above is the most
> > straightforward.
> >
> > - Sahil Malik [MVP]
> > http://codebetter.com/blogs/sahil.malik/
> >
> >
> >
> > "Dave" <D***@discussions.microsoft.com> wrote in message
> > news:5C1DE540-70F3-4D00-83D9-8C529D35D183@microsoft.com...
> > > Hi,
> > >
> > > I've managed to populate a dataset with two different datatables from
> > > different webservices. They each contain a different number of
columns
> > but
> > > both have the Ticker symbol as a common key.
> > >
> > > Is there a way to "join" or combine these the two tables based on the
> > ticker
> > > into one? I only want to return rows from each where there is a match
on
> > > ticker between the two (like an inner join).
> > >
> > > I've tried creating a DataRelation but the datatable I had setup as
the
> > > parent returns all it's rows when I bind it to a datagrid even though
> > there
> > > are no matching children. So, I'm not sure if this object is the way
to
> > go.
> > >
> > > If I'm able to combine the datatables into a single one, I want to
filter
> > it
> > > at that point.
> > >
> > > Thanks, Dave
> > >
> > >
> >
> >
> >

5 Apr 2005 9:13 PM

Dave

Ok, thanks, but if you mean a DataRelation, I've already tried that but it
nests the datatables in a hierarchial manner. Where I want to make a "flat"
resultset between the two. I guess I don't quite understand how I would do
the find you describe.

Show quoteHide quote

"Sahil Malik [MVP]" wrote:

> Another approach could be to have a seperate table with all the unique
> ticker values, and then setup a relationship with the two tables.
>
> TickerMapId, TIcker1, Ticker2.
>
> And once you setup this relation, (This'd be somewhat like a nullable FK),
> you can find where Ticker1 = Ticker2 - and find childrows based on that.
>
> This approach has an initial setup cost - but will work MUCH faster than
> DataTable.Select, and is more suitable to a situation where you have
> multiple requests to the same two datatables you've prepared.
>
> I am sure there are even other approaches - it totally depends on how
> creative you wanna be. :)
>
> - Sahil Malik [MVP]
> http://codebetter.com/blogs/sahil.malik/
>
>
>
>
>
> "Dave" <D***@discussions.microsoft.com> wrote in message
> news:7C0A0F02-9BA6-43E6-81E2-616E82CA3216@microsoft.com...
> > Thanks! You're right. It would be just like a SQL Join on ticker, except I
> > have these two datatables loaded with XML from external separate sources.
> I
> > want to join them into one result set somehow.
> >
> > I'd be interested in other ideas too. When I started this, I thought the
> > hard part was getting the datatables loaded but now I'm kind of stuck
> getting
> > getting them joined/combined on the common key.
> >
> > Thanks, Dave
> >
> > "Sahil Malik [MVP]" wrote:
> >
> > > Okay so if I understood your question correctly,
> > >
> > > You have two tables. They have a common column. And you want to find all
> > > rows, where that particular column value is the same in either table.
> Right?
> > >
> > > Hmm ... Merge is not what you need then .. IMO.
> > >
> > > You can however use DataTable.Select. The where clause you formualte for
> the
> > > the Select method, will have to be formed over a loop from the first
> table.
> > >
> > > So something like ..
> > >
> > > Table2.Select("Ticker In (1,2,3,4)")
> > >
> > > Where 1,2,3,4 are formed over a loop from Table1.Ticker.
> > >
> > > BTW - I must add - DataTable.Select isn't the fastest pony in the
> stable, so
> > > hopefully you don't have a lot of rows.
> > >
> > > There are other ways to do this too - but the above is the most
> > > straightforward.
> > >
> > > - Sahil Malik [MVP]
> > > http://codebetter.com/blogs/sahil.malik/
> > >
> > >
> > >
> > > "Dave" <D***@discussions.microsoft.com> wrote in message
> > > news:5C1DE540-70F3-4D00-83D9-8C529D35D183@microsoft.com...
> > > > Hi,
> > > >
> > > > I've managed to populate a dataset with two different datatables from
> > > > different webservices. They each contain a different number of
> columns
> > > but
> > > > both have the Ticker symbol as a common key.
> > > >
> > > > Is there a way to "join" or combine these the two tables based on the
> > > ticker
> > > > into one? I only want to return rows from each where there is a match
> on
> > > > ticker between the two (like an inner join).
> > > >
> > > > I've tried creating a DataRelation but the datatable I had setup as
> the
> > > > parent returns all it's rows when I bind it to a datagrid even though
> > > there
> > > > are no matching children. So, I'm not sure if this object is the way
> to
> > > go.
> > > >
> > > > If I'm able to combine the datatables into a single one, I want to
> filter
> > > it
> > > > at that point.
> > > >
> > > > Thanks, Dave
> > > >
> > > >
> > >
> > >
> > >
>
>
>

5 Apr 2005 10:06 PM

Sahil Malik [MVP]

Not just a datarelation. You will have to add a brand new table, and two
datarelations per what I described. It should work I think.

--

- Sahil Malik [MVP]
http://codebetter.com/blogs/sahil.malik/

Show quoteHide quote

"Dave" <D***@discussions.microsoft.com> wrote in message
news:4715A646-EDCE-4F1C-AC84-78C1BCB9A18C@microsoft.com...
> Ok, thanks, but if you mean a DataRelation, I've already tried that but it
> nests the datatables in a hierarchial manner. Where I want to make a
"flat"
> resultset between the two. I guess I don't quite understand how I would
do
> the find you describe.
>
> "Sahil Malik [MVP]" wrote:
>
> > Another approach could be to have a seperate table with all the unique
> > ticker values, and then setup a relationship with the two tables.
> >
> > TickerMapId, TIcker1, Ticker2.
> >
> > And once you setup this relation, (This'd be somewhat like a nullable
FK),
> > you can find where Ticker1 = Ticker2 - and find childrows based on that.
> >
> > This approach has an initial setup cost - but will work MUCH faster than
> > DataTable.Select, and is more suitable to a situation where you have
> > multiple requests to the same two datatables you've prepared.
> >
> > I am sure there are even other approaches - it totally depends on how
> > creative you wanna be. :)
> >
> > - Sahil Malik [MVP]
> > http://codebetter.com/blogs/sahil.malik/
> >
> >
> >
> >
> >
> > "Dave" <D***@discussions.microsoft.com> wrote in message
> > news:7C0A0F02-9BA6-43E6-81E2-616E82CA3216@microsoft.com...
> > > Thanks! You're right. It would be just like a SQL Join on ticker,
except I
> > > have these two datatables loaded with XML from external separate
sources.
> > I
> > > want to join them into one result set somehow.
> > >
> > > I'd be interested in other ideas too. When I started this, I thought
the
> > > hard part was getting the datatables loaded but now I'm kind of stuck
> > getting
> > > getting them joined/combined on the common key.
> > >
> > > Thanks, Dave
> > >
> > > "Sahil Malik [MVP]" wrote:
> > >
> > > > Okay so if I understood your question correctly,
> > > >
> > > > You have two tables. They have a common column. And you want to find
all
> > > > rows, where that particular column value is the same in either
table.
> > Right?
> > > >
> > > > Hmm ... Merge is not what you need then .. IMO.
> > > >
> > > > You can however use DataTable.Select. The where clause you formualte
for
> > the
> > > > the Select method, will have to be formed over a loop from the first
> > table.
> > > >
> > > > So something like ..
> > > >
> > > > Table2.Select("Ticker In (1,2,3,4)")
> > > >
> > > > Where 1,2,3,4 are formed over a loop from Table1.Ticker.
> > > >
> > > > BTW - I must add - DataTable.Select isn't the fastest pony in the
> > stable, so
> > > > hopefully you don't have a lot of rows.
> > > >
> > > > There are other ways to do this too - but the above is the most
> > > > straightforward.
> > > >
> > > > - Sahil Malik [MVP]
> > > > http://codebetter.com/blogs/sahil.malik/
> > > >
> > > >
> > > >
> > > > "Dave" <D***@discussions.microsoft.com> wrote in message
> > > > news:5C1DE540-70F3-4D00-83D9-8C529D35D183@microsoft.com...
> > > > > Hi,
> > > > >
> > > > > I've managed to populate a dataset with two different datatables
from
> > > > > different webservices. They each contain a different number of
> > columns
> > > > but
> > > > > both have the Ticker symbol as a common key.
> > > > >
> > > > > Is there a way to "join" or combine these the two tables based on
the
> > > > ticker
> > > > > into one? I only want to return rows from each where there is a
match
> > on
> > > > > ticker between the two (like an inner join).
> > > > >
> > > > > I've tried creating a DataRelation but the datatable I had setup
as
> > the
> > > > > parent returns all it's rows when I bind it to a datagrid even
though
> > > > there
> > > > > are no matching children. So, I'm not sure if this object is the
way
> > to
> > > > go.
> > > > >
> > > > > If I'm able to combine the datatables into a single one, I want to
> > filter
> > > > it
> > > > > at that point.
> > > > >
> > > > > Thanks, Dave
> > > > >
> > > > >
> > > >
> > > >
> > > >
> >
> >
> >