Returning a single recordset of data from two related tables in a Dataset

[Steven J Jowett]

I have a dataset containing 2 tables, PLSupplierAccount and PLProposedPayment, which contain related data. I have created a relationship between the two table. I now need to execute the following sql statement, and return a single recordset/table of the data.

SELECT * FROM PLSupplierAccount INNER JOIN PLProposedPayment ON PLSupplierAccount.PLSupplierAccountID = PLProposedPayment.PLSupplierAccountID

Not sure how to do this, and Google[^]"> was not much help. Any help appreciated (as always)

Steve Jowett ------------------------- It is offen dangerous to try and see someone else's point of view, without proper training. Douglas Adams (Mostly Harmless)

[Ashfield]

Try this:

SELECT PLSupplierAccount.*,PLProposedPayment.*
FROM PLSupplierAccount INNER JOIN PLProposedPayment ON PLSupplierAccount.PLSupplierAccountID = PLProposedPayment.PLSupplierAccountID

But change PLSupplierAccount.*,PLProposedPayment.* for the columns you really need - its not good practice to do a select *, although I often use it for code examples ;)

Bob Ashfield Consultants Ltd

[Steven J Jowett]

Bob Thanks for the reply, but it's not the SQL statement I am having trouble with. I need to return the data from a DataSet i.e. execute the select statement against the dataset and get the results as a single datatable.

Steve Jowett ------------------------- It is offen dangerous to try and see someone else's point of view, without proper training. Douglas Adams (Mostly Harmless)

[Ashfield]

Sorry, I misunderstood. Personally I would be tempted to requery the database and create the new table that way. This may help, but I have not tried it myself. I'm kind of old fashioned, I like to let sql do the work for me whenever I can :) Microsoft Article

Bob Ashfield Consultants Ltd

[BDEz Member 3919223]

so you need to drop the combined dataset into an array, then loop through the array and assign each line to a pre-built internal data table?

[Ashfield]

Thats Microsoft's solution not mine :) I would requery the database and create the new table that way

Bob Ashfield Consultants Ltd

[Steven J Jowett]

Ideally I would also requery the database (and normally would), but in this case I need to load a subset of data from the database, make some changes to it and when the user is happy with the changes, submit them back. Thanks for the help and advise

Steve Jowett ------------------------- It is offen dangerous to try and see someone else's point of view, without proper training. Douglas Adams (Mostly Harmless)

[BDEz Member 3919223]

I failed to mention two wonderful classes that you can use to do what you want quickly, one is dataset and the other is dataview and they should not be overlooked because they can help you out. Public Class DataView Inherits System.ComponentModel.MarshalByValueComponent Member of: System.Data Summary: Represents a databindable, customized view of a System.Data.DataTable for sorting, filtering, searching, editing, and navigation. Public Class DataSet Inherits System.ComponentModel.MarshalByValueComponent Member of: System.Data Summary: Represents an in-memory cache of data.

[Ashfield]

That was (or still is) the problem, Steve has a dataset with 2 datatables that he wants to query with a join. There is no real support within the dataset/datatable/datarelation world to do this easily.

Bob Ashfield Consultants Ltd

[Ashfield]

Sometimes life's a bitch..... Just a thought, would linq help? I have only played with it briefly, but it might be worth a look?

Bob Ashfield Consultants Ltd

[Steven J Jowett]

I've not even had time to look at LINQ, but I think I will make the time.

Steve Jowett ------------------------- It is offen dangerous to try and see someone else's point of view, without proper training. Douglas Adams (Mostly Harmless)

[BDEz Member 3919223]

Lets see if I can think this through ... We point / read a dataset (call it dsOne) to a table from your SQL? server. We point / read a second dataset (call it dsTwo) to another table from your SQL server. We point a dataview (call it dvOne) to dsOne We do a sort on dvOne on PLSupplierAccountID We pull the first entry for PLSupplierAccountID in dvOne We point a second dataview (call it dvTwo) to dsTwo We use the entry from dv.One SupplierAccountID to do a dvtwo.Find If we get a match we combine the row entry from dvOne with dvTwo in a DataGrid Once you get all the matches combined in the datagrid, you can make your changes using your form. These changes will be stored on the form until you "submit" them back to your SQL server using a update query. This sound about right to you?

[BDEz Member 3919223]

Looks interesting! I will have to play with this!