I have 3 tables:Article,Source and File.
Each article can have multiple filenames.
The fields of table Article are:ArticleID,SourceID,ArticleDate,ArticleCategory
The fields of table Source areourceID,SourceName
the fields of Table File:ID,ArticleID,Filename
Select*from Article inner join Source on Article.SourceID=source.SourceID order by ArticleDate
I obtain a number of recordsets from the above query.
Then for each recordset(Rs1),Let's say for the first recordset
I want to apply this query:
Rs2.Open"Select SourceName,ArticleDate,File.Filename from [RS1] inner Join File on Article.ArticleID=File.ArticleID
I want from the above query to have the Filenames corresponding to each Article because in my VB
form I have 2 command buttonsne gives me the article's definition(Date,Source)
and the other gives me the Filename of the current recordset(Article)
The above SQL syntax is it correct?
why not just join all 3 tables in your 1st query
select a.ArticleID, a.SourceID, s.SourceName, a.ArticleDate, a.ArticleCategory, f.ID, f.Filename
from Article a inner join Source s
on a.SourceID = s.SourceID
inner join File f
on a.ArticleID = f.ArticleID
order by a.ArticleDate
Just as an FYI - you cannot use a recordset in a SQL statement. There are, I believe, 3rd party utilities that allow you to join recordsets and query them a little like you are trying to do but I don't know how well these perform (quite badly I would imagine) and I have never come accross a situation where they are necessary.