Select one row from first table then join multiple

Question

I have the following tables,

clients

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

press releases

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 content

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

I want to write a query that basically selects the latest press release for each client. The list will only return 1 client for each press release

Thanks a million

score 0 · Answer 1 · answered Nov 19 '12 at 13:31

Try this:

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

score 0 · Answer 2 · edited May 23 '17 at 12:18

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

Select one row from first table then join multiple

2 Answers2