[gk]

I want to do a query on items but also have groups listed and sorted by name. My tables are:

groups
---------
id
name

items
---------
id
name
group_id

and I have an sql statement like:

SELECT `items`.`id`,`items`.`name` FROM  `items` LEFT JOIN `groups` ON `items`.`group_id`=`groups`.`id` ORDER BY `name`LIMIT 10,10 

but that doesn't work. what I need is to have groups.name and items.name combined and sorted alphabetically.

I can think of one way to do it (dumping the results to a temporary table then sort in another select) but am wondering which is the better or if there is a another way.

[gray]

off the top of my head, without reading fully into what you want, and after a few mojitos (this is gonna be helpful then)

have you tried GROUP BY?

[gk]

I don't think GROUP BY is an option. An Item may not be part of a group leaving its group field as 0. The GROUP BY option would combine all these items would it not?

[LazyMoon]

I'm not too sure what you actually want from your SQL. What do you mean by "what I need is to have groups.name and items.name combined and sorted alphabetically"?

Can you give us an example of what you want.

If your trying to sort multiple columns you can try:

SELECT `items`.`id`,`items`.`name`
FROM  `items`
LEFT JOIN `groups` ON `items`.`group_id`=`groups`.`id`
ORDER BY `items`.`group_id` ASC, `items`.`name` ASC
LIMIT 10,10