dataset

[betul]

Hello,I have two tables in ms access; writers and articles and want to list latest article of each writer by a datalist. (A writer has many articles.) But it lists all the articles. Here is the code;

OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM writers INNER JOIN articles ON writers.author_id=articles.author_id", conn);adapter.Fill(dataset, "writers");writersList.DataSource = new DataView(dataset.Tables[0]);writersList.DataBind();

In the item template I use fields of both tables. I can't limit second table.

[Reg Edit]

Hello,I have two tables in ms access; writers and articles and want to list latest article of each writer by a datalist. (A writer has many articles.) But it lists all the articles. Here is the code;

OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM writers INNER JOIN articles ON writers.author_id=articles.author_id", conn);...

Well, presumably articles has an article_date field? You'll need to refer to that, to select the latest article. You could either load the articles table separately into the dataset, and set bindings/relationships to suit, or, if you want it all in the one dataset table, you'll need a subquery:

SELECT * FROM writers w INNER JOIN articles a ON w.author_id=a.author_idWHERE a.article_date = 	( SELECT max(article_date) FROM articles		  WHERE author_id = w.author_id )

[betul]

Well, presumably articles has an article_date field? You'll need to refer to that, to select the latest article. You could either load the articles table separately into the dataset, and set bindings/relationships to suit, or, if you want it all in the one dataset table, you'll need a subquery:

SELECT * FROM writers w INNER JOIN articles a ON w.author_id=a.author_idWHERE a.article_date = 	( SELECT max(article_date) FROM articles		  WHERE author_id = w.author_id )

Thank you! Since an author may send more articles in the same day and my article_date field doesn't contain time, I changed it to max(article_id). Thank you very much for your help.