I have 2 tables as following :
tbl_Articles
3 ArticleID int
0 AuthorID int
0 ArticleTitle
0 ArticleText
0 ArticleDate
tbl_Authors
3 AuthorID
0 AuthorFullName
0 AuthorEmail
0 AuthorDescription
0 AuthorImage
I want to write a query to see the Authors and their last articles with no distinct values.
Like AuthorImage - AuthorFullName - ArticleTitle - ArticleDate
If anyone knows the solution i will be glad .
Thanks from now onselect AuthorImage, AuthorFullName, ArticleTitle, ArticleDate = aDate
from tbl_Authors a
inner join (
select AuthorID, aDate = max(ArticleDate)
from tbl_Articles) x
on a.AuthorID = x.AuthorID
inner join tbl_Articles b
on x.AuthorID = b.AuthorID
and x.aDate = b.ArticleDate|||another version:select AuthorImage
, AuthorFullName
, ArticleTitle
, ArticleDate
from tbl_Authors AUTH
inner
join tbl_Articles ART
on AUTH.AuthorID
= ART.AuthorID
where ART.ArticleDate
= ( select max(ArticleDate)
from tbl_Articles
where AuthorID
= AUTH.AuthorID )
Tuesday, March 27, 2012
a Distinct Query
Labels:
articledate,
articleid,
articletext,
articletitle,
authorid,
database,
distinct,
following,
int,
microsoft,
mysql,
oracle,
query,
server,
sql,
tables,
tbl_articles3,
tbl_authors3
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment