Csabi Csabi - 11 days ago 6
MySQL Question

How to order by items based on data from two mysql tables?

I have two tables: users (username,points,....) and items(id,author-username,....)
Now I have only this:

(CASE WHEN title LIKE '%" . $keyword . "%' THEN 1 ELSE 0 END)
- I check an element for multiple keywords
But I want to sort the items based on how related is to
$keyword
and the same time how much points the author of the item has.

For example if an item is related to 2 keywords and the author of it has 5 points the item will be rated with 7 points

How it`s possible to do this ? Any ideas ?

Answer

You can aggregate a custom sort field from a number of data fields or computations, e.g.:

SELECT
  a.name,
  b.score,
  CONCAT(a.company, FORMAT(b.score * b.handicap, 0)) AS sortfield
FROM a,b
WHERE ...
SORT BY sortfield