从第一个表中选择一行，然后连接多个

Question

我有下面的表格，

客户端

client_id | client_name
2         | client 1
3         | client 2
4         | client 3

新闻稿

press_release_id | date                  | title                 | client_id
1                | 2012-10-16 00:00:00   | new press release     | 3
2                | 2012-10-16 00:00:00   | worlwide news         | 2

新闻稿内容

press_release_id | content | translation
1                | content | en
2                | content | fr

我想写一个查询，基本上为每个客户选择最新的新闻稿。对于每个新闻稿，该列表将仅返回一个客户端

万分感谢

Answer 1

试试这个：

SELECT 
  c.client_name,
  pr.date,
  pr.title, 
  pc.content,
  pc.translation
FROM clients c
INNER JOIN pressreleases pr ON c.client_id = pr.client_id
INNER JOIN
(
   SELECT 
     press_release_id, MAX(date) LatestDate
   FROM pressreleases 
   GROUP BY press_relaese_id
) pl ON pr.press_release_id = pl.press_release_id AND pr.date = pl.latestdate
INNER JOIN press releasecontent pc ON pr.press_release_id = pc.press_release_id

Answer 2

SELECT  a.*, b.*, d.*
FROM    clients a
        INNER JOIN pressReleases b
            ON a.client_ID = b.client_ID
        INNER JOIN
        (
            SELECT client_ID, MAX(date) maxDate
            FROM pressReleases
            GROUP BY client_ID
        ) c ON b.date = c.mAxDate AND
                b.client_ID = c.client_ID
        INNER JOIN pressReleaseContent d
            ON b.press_release_id = d.press_release_id

• SQLFiddle Demo ()
• Previous Answer (browse for )