How to sort by a field from another table?

Question

MySQL

There are two tables users, f_list, you need to select all fields from f_list and sort them by the last_visit field from the users table if the id from users is equal to the id_sender or id_receiver of the record from f_list. In short, if you simply need to sort the records by the last time the user was on the site.

SELECT f.*, u.last_visit
FROM f_list f, users u
WHERE f.id_receiver = 11 or f.id_sender = 11
ORDER BY u.last_visit DESC

I tried to give it out in the correct order, but for some reason it duplicates the records 6 times.
If possible with an example.

Answer 1

SELECT f.*, u.last_visit
  FROM f_list f, users u
 WHERE u.id = f.id_sender AND f.id_receiver = 11
 UNION
SELECT f.*, u.last_visit
  FROM f_list f, users u
 WHERE u.id = f.id_receiver AND f.id_sender = 11
 ORDER BY last_visit desc

sqlfiddle.com/#!9/cb52eb/2
You tried to mix sender/receiver into one heap, and these are two joins, generally speaking. Because interested in the current user, it's easier to do one join + union.

Answer 2

you display ALL users for each entry from f_list (filtered by the condition in where), so they are multiplied
sql this is about multiplying tables against each other, when they are specified in from
, you must specify the condition how the entries from f_list are related to users, usually this is a foreign key indexes (or at you there still a plate intermediate)
so the scheme in studio, describe sense of the stored data in your tables and that you want to receive.