mysql连接查询和排序,MySql查询限制和排序连接状态

I have a two tables as authors and articles. I want to get list of latest articles for each author. I want only one article for one author. And I want it to be the latest. But, I couldn't even figure out where to start to this sql query.

Edit

My table structure can be simplefied like this:

authors:

id

name

status

seo

articles:

author_id

title

text

date

seo

Edit 2

I came up with something like this, is there any obvious mistakes you can see in here:

SELECT authors.*,

(SELECT articles.title FROM articles WHERE author_id = authors.id ORDER BY articles.date DESC LIMIT 1) as title,

(SELECT articles.seo FROM articles WHERE author_id = authors.id ORDER BY articles.date DESC LIMIT 1) as articleseo

FROM authors

WHERE authors.status = 1

解决方案

Alright, I found out what I needed to do:

CREATE TEMPORARY TABLE articles2

SELECT max(date) as maxdate, author_id

FROM articles

GROUP BY author_id;

SELECT authors.name, authors.seo, articles.seo, articles.title FROM articles JOIN articles2 ON (articles2.author_id = articles.author_id AND articles2.maxdate = articles.date) JOIN authors on authors.id = articles.author_id WHERE authors.status = 1

I hope this helps someone.