Selecting from two tables, limiting by the first

Question

I've been looking for the solution for several days, and I'm starting to feel like a real idiot.

I have two tables.

entries                       link
+----------+-------+-----+    +----------+------+
| date     | title | txt |    | date     | tag  |
+==========+=======+=====+    +==========+======+
|2014-01-01| titl1 | txt1|    |2014-01-01| tag1 |
|2014-01-02| titl2 | txt2|    |2014-01-01| tag2 |
|2014-01-03| titl3 | txt3|    |2014-01-01| tag3 |
|2014-01-04| titl4 | txt4|    |2014-01-02| tag1 |
|2014-01-05| titl4 | txt4|    |2014-01-02| tag2 |
|2014-01-06| titl4 | txt4|    |2014-01-03| tag1 |
|2014-01-07| titl4 | txt4|    |2014-01-04| tag2 |
|2014-01-08| titl4 | txt4|    |2014-01-04| tag4 |
+----------+-------+-----+    +~~~~~~~~~~+~~~~~~+ 'link' continues

I want * from 'entries', ordered by 'date', limited by 4, as well as all corresponding 'tag's from 'link' for each entry.

'date' from 'entries' is a primary key, if that's important. 'link' doesn't have primary keys, as there will be duplicates of both 'date' and 'tag'.

Posting here was a last resort, as I generally hate relying on help to get something done, but I'm completely run out of mental capacity to keep trying.

I've got this query, which grabs one entry and the corresponding tag, and limits by 4, but it doesn't grab /all/ tags per entry.

SELECT date, title, txt
FROM entries
WHERE date IN (
    SELECT date
    FROM link
    WHERE date = entries.date
)
LIMIT 4

If I 'reverse' this and try,

SELECT date, tag
FROM link
WHERE date IN (
    SELECT date
    FROM entries
    WHERE date = link.date
)
LIMIT 4

It limits by four rows from 'link'.

I really hope I've explained what I want properly. If I wasn't clear, I can try again.

Answer 1

I got what I wanted with,

SELECT *
FROM (
    SELECT date AS date
    FROM entries
    ORDER BY date DESC
    LIMIT 4
) AS ent, link
WHERE ent.date = link.date

This gives me entries and all their tags, and limits to four entries, without cutting the tags off.

It outputs a pattern similar to:

2014-01-04 tag4
2014-01-04 tag2
2014-01-03 tag1
2014-01-02 tag2
2014-01-02 tag1
2014-01-01 tag3
2014-01-01 tag2
2014-01-01 tag1

Which is what I wanted.

I took a few hours to just calm down and just walk away from the code, and sometimes, that's all you need.