MySQL Query: LIMITing a JOIN

greatest-n-per-group, join, mysql, sql

Solution

Just done a quick test. This seems to work:

mysql> select * from categories c, items i
    -> where i.categoryid = c.id
    -> group by c.id;
+------+---------+------+------------+----------------+
| id   | name    | id   | categoryid | name           |
+------+---------+------+------------+----------------+
|    1 | Cars    |    1 |          1 | Ford           |
|    2 | Games   |    4 |          2 | Tetris         |
|    3 | Pencils |    6 |          3 | Pencil Factory |
+------+---------+------+------------+----------------+
3 rows in set (0.00 sec)

I think this would fulfil your first question. Not sure about the second one - I think that needs an inner query with order by random() or something like that!

Problem

Say I have two tables I want to join. Categories: ``` id name ---------- 1 Cars 2 Games 3 Pencils ``` And items: ``` id categoryid itemname --------------------------- 1 1 Ford 2 1 BMW 3 1 VW 4 2 Tetris 5 2 Pong 6 3 Foobar Pencil Factory ``` I want a query that returns the category and the first (and only the first) itemname: ``` category.id category.name item.id item.itemname ------------------------------------------------- 1 Cars 1 Ford 2 Games 4 Tetris 3 Pencils 6 Foobar Pencil Factory ``` And is there a way I could get random results like: ``` category.id category.name item.id item.itemname ------------------------------------------------- 1 Cars 3 VW 2 Games 5 Pong 3 Pencils 6 Foobar Pencil Factory ``` Thanks!

Original source