ORDER BY another table.

[R4v]

Hi,

I am struggling with the following problem and need some expert help. I hope it makes sense.

I have to mysql tables.

useritems: id userid itemid
items: id name

I have a standard query that selects all fields from the user items field..

"SELECT `id`, `userid`, `itemid` FROM `useritems` WHERE `id` = '3'"

This works just fine BUT I want to have the results ordered by their name (in the items table). The itemid in the useritems table, refers to the id in the items table.

so something like..

"SELECT `id`, `userid`, `itemid` FROM `useritems` WHERE `id` = '3' ORDER BY (name from the other table) ASC;"

Now I figure this requires a left join but just cannot get it working.

Any help is appreciated.

[johanafm]

FROM useritems
INNER JOIN items ON itemid = items.id
WHERE ...
ORDER BY name

Left join is only useful if you have itemid in useritems that have no match on id in items. In that case all the fields in the select clause that comes from items would be set to null.
The inner join will simply not inlcude any such results. That is, it requires that there is a matching itemid and items.id

[R4v]

Thanks, put me in the right direction.

Had to change slightly as both tables had an id field..

a.id, itemid, equipped, AS a ....... a.itemid

works a treat.

🙂