How to sort items in mysql based on data from another table?

I have the following tables:

word_list:

id | word
1  | ball
2  | car 
3  | small

items_word_match:

itemid | wordid | in_title | in_description
1      | 1      | 1        | 0 //The word #1 occurs once in the title of the item 
1      | 3      | 0        | 2 //The word #3 occurs twice in the title of the item
3      | 3      | 1        | 2
2      | 1      | 1        | 0

search:

wordid | importance 
1      | 1
2      | 5 
3      | 2 //word #3 is more important than the word #1 but less so than word #2

I want to sort the items based on the keywords from the search table and how important the keywords are.

And if the keyword is in the title the importa开发者_开发知识库nce should increase by 1 and if the word appears 2 times the importance of the word should be importance*2

Neither of those answers from Denis or Johan are correct. Instead you could use this:

select 
    itemid, 
    sum(word_importance) as item_importance
from
    (select 
        itemid, 
        search.wordid,
        (
            in_title * (importance + 1)
            + in_description * importance
        ) as word_importance
    from 
        items_word_match, 
        search 
    where 
        i.wordid = s.wordid 
    )
group by itemid

As Johan pointed out, you need to add an order clause to the end, , order by item_importance desc

Feel a bit lazy today, so I'm just going to answer the question in the title:

How to sort items in mysql based on data from another table ?

You can sort the outcome of a query by any criterium you wish.

SELECT word_list.* FROM word_list
INNER JOIN search ON (search.wordid = wordlist.id)
ORDER BY search.importance, word_list.id DESC

Note that the JOIN needed to link the two tables together can have a profound effect on which rows in the word_list table are selected, but you need to do a JOIN somehow.
Otherwise MySQL will not know what the relationship between the two tables is and cannot sort the fields.

SELECT
    i.itemid
  , SUM( i.in_description * s.importance 
       + i.in_title * ( s.importance + 1 )
       )
    AS item_importance
FROM
    items_word_match i
        LEFT JOIN
    search s
          ON s.wordid = i.wordid
GROUP BY
    i.itemid
ORDER BY
    item_importance DESC

CORRECTION:

I used LEFT JOIN catch the case when some words do not appear in the search table. But then the importance of those words seems appropriate to be 0 and not NULL, so the SUM should be changed into:

  , SUM( i.in_description * COALESCE(s.importance, 0) 
       + i.in_title * COALESCE(s.importance, 1) 
       )

your order by clause can contain fields from any of the tables:

select table1.*
from table1
join table2 using (table1_id)
order by table2.field, table1.field, etc.