views:

673

answers:

3

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!

A: 

Mysql lets you to have columns not included in grouping or aggregate, in which case they've got random values:

    select category.id, category.name, itemid, itemname
    inner join 
      (select item.categoryid, item.id as itemid, item.name as itemname
       from item group by categoryid)
    on category.id = categoryid

Or, for minimums,

select category.id, category.name, itemid, itemname
inner join 
  (select item.categoryid, min(item.id) as itemid, item.name as itemname
  from items
  group by item.categoryid)
on category.id = categoryid
GSerg
I got an error when I tried this method: "Every derived table must have its own alias".I guess I did something wrong somewhere.
Eikern
+4  A: 

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!

Phill Sacre
Having the results being random isn't that important to me, so this was really what I was looking for! Thanks
Eikern
A: 

Mysql does let include non aggregate columns and there is no guarantee of determinism, but in my experience I nearly always get the first values.

So usually (but not guaranteed) this will give you the first

select * 
from categories c, items i
where i.categoryid = c.id
group by c.id;

If you want guaranteed you will need to do something like

select categories.id, categories.name, items.id, items.name
from categories inner join
  items on items.categoryid = categories.id and 
     items.id = (select min(items2.id) from items as items2 where items2.categoryid = category.id)

If you want random answers you will have to change the subquery a little bit

 select categories.id, categories.name, items.id, items.name
    from categories inner join
      items on items.categoryid = categories.id and 
         items.id = (select items2.id from items as items2 where items2.categoryid = category.id order by rand() limit 1)