JOIN queries

  • Thread starter Thread starter milese
  • Start date Start date
M

milese

I want to run a query such as the following:
select * from table1 left join table2 on table1.id=table2.table1_id left join table3 on table1.id=table3.table1_id where table1.id=X
Click to expand...

I want to stuff the results in a DataSet and be able to access field
values like the following:

dataSet.Table["table1"].Rows[0]["field1"]

Currently, the DataSet is flat containing all fields from the 3 tables.
So accessing a field looks like:

dataSet.Table[0].Rows[0]["field1"]


I'd like a DataSet with 3 Tables. The reason is the tables may have
field names which are the same. Under the 1 flat table scenario, field
names that are the same get a number added to the end, such as
"field11". A DataSet with 3 tables generated would be more 'elegant'.

Any suggestion? Thanks in advance.
 
Read each table into a DataTable (and put it in your dataset).

Then, add constraints to link the tables together.

I want to run a query such as the following:
select * from table1 left join table2 on table1.id=table2.table1_id left
join table3 on table1.id=table3.table1_id where table1.id=X
Click to expand...

I want to stuff the results in a DataSet and be able to access field
values like the following:

dataSet.Table["table1"].Rows[0]["field1"]

Currently, the DataSet is flat containing all fields from the 3 tables.
So accessing a field looks like:

dataSet.Table[0].Rows[0]["field1"]


I'd like a DataSet with 3 Tables. The reason is the tables may have
field names which are the same. Under the 1 flat table scenario, field
names that are the same get a number added to the end, such as
"field11". A DataSet with 3 tables generated would be more 'elegant'.

Any suggestion? Thanks in advance.
Click to expand...
 
"Read each table ...". Read each table with separate select
statements? I thought about that but I was hoping to do this with 1
trip to the database.

I'll give this a shot, thanks.


Brendan said:
Read each table into a DataTable (and put it in your dataset).

Then, add constraints to link the tables together.

I want to run a query such as the following:
select * from table1 left join table2 on table1.id=table2.table1_id left
join table3 on table1.id=table3.table1_id where table1.id=X
Click to expand...

I want to stuff the results in a DataSet and be able to access field
values like the following:

dataSet.Table["table1"].Rows[0]["field1"]

Currently, the DataSet is flat containing all fields from the 3 tables.
So accessing a field looks like:

dataSet.Table[0].Rows[0]["field1"]


I'd like a DataSet with 3 Tables. The reason is the tables may have
field names which are the same. Under the 1 flat table scenario, field
names that are the same get a number added to the end, such as
"field11". A DataSet with 3 tables generated would be more 'elegant'.

Any suggestion? Thanks in advance.
Click to expand...
Click to expand...
 
Back
Top