mysql select join: one-to-many relationship

Question

i have 2 tables

table: wiki_articles

wiki_article_id | title      | slug
---------------------------------------
1               | title 1    | title-1
2               | title 2    | title-2

table: wiki_articles_text

wiki_article_text_id | wiki_article_id | text | timestamp
---------------------------------------------------------
1                    | 1               | ...  | 2017-04-25 12:00:00
2                    | 1               | ...  | 2017-04-25 12:30:00
3                    | 2               | ...  | 2017-04-25 13:00:00
4                    | 2               | ...  | 2017-04-25 13:30:00

I want to select all the articles together with the latest text for each article.

My query so far:

        $q = "  SELECT      a.wiki_article_id,
                            a.title,
                            a.slug,
                            t1.text,
                            t1.timestamp
                FROM
                            wiki_articles AS a
                JOIN
                            wiki_articles_texts AS t1
                        ON
                            a.wiki_article_id = t1.wiki_article_id
                LEFT OUTER JOIN
                            wiki_articles_texts AS t2
                        ON
                            (a.wiki_article_id = t2.wiki_article_id AND (t1.timestamp < t2.timestamp OR t1.timestamp = t2.timestamp AND t1.wiki_article_text_id < t2.wiki_article_text_id))
                WHERE
                            t2.wiki_article_text_id IS NULL";

This does not work. I've used this (SQL join: selecting the last records in a one-to-many relationship) question to try, because I think it's the same problem.

What's wrong with my query?

Edit: I found a bug, it works now. I forgot to add '1' to t1.wiki_article_id

score 0 · Accepted Answer · answered Apr 25 '17 at 18:25

You could use a inner join on max my_timestamp group by wiki_article_id

$q = "  SELECT      a.wiki_article_id,
                        a.title,
                        a.slug,
                        t1.text,
                        t1.timestamp
            FROM    wiki_articles AS a
            JOIN    wiki_articles_texts AS t1  ON  a.wiki_article_id = t1.wiki_article_id
            INNER   JOIN ( 
              select wiki_article_id, max(timestamp) as  my_timestamp
              from wiki_articles_texts 
              group by wiki_article_id
     ) t2 on  a.wiki_article_id = t2.wiki_article_id and t1.timestamp = t1.my_timestamp";

mysql select join: one-to-many relationship

1 Answers1