Okay, I really don't get this. I'll provide a little more information in hopes that someone can help me. The "favourites" table has two feilds: "id" and "fav_id". The person identified by "id" has the person identified by "fav_id" on thier favourites list. Niether feild is unique because person identified by "id" may have more than one favourite on his list, just like the person identified by "fav_id" may be on more than one person's favourites list. The table "users" contains all the user information(username, password, location, etc), and one the feilds is called "popularity". The highesr than number is, the better.
Now, a hypothetical situation: Someone vistits A's profile, and A has B, C, D, and E on his favourites list. B has a popularity of 10, C has a popularity of 50, D has a popularity of 2, and E has a popularity of 14. When the favourites are being listed, I want to display C first, followed by E, then B, and finally D.
Structuring the query like the following
Code: Select all
SELECT * FROM `users`, `favourites` WHERE `favourites`.`id`="1" ORDER BY `users`.`popularity` DESC
gives me a myriad of irrelevant results, and I can see why. MySQL has no way of knowing whose popularity I'm referring to. I don't understand how to fix that. I read that article about the JOIN sytax, but I'm still quite confused.
And yeah, on second though, this should probably be in Databases.
Thanks for the help.